SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query using multiple counts with conditions
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

cincyKid
Starting Member

22 Posts

Posted - 05/05/2012 :  18:01:18  Show Profile  Reply with Quote
no, LISTID is primary key for the LIST table. I just need that primary key select to be worked in to my query without having to add it to the GROUP BY. I dont see how an aggregate will work since i need the exact ID, not min, max, sum, count, etc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/05/2012 :  18:08:30  Show Profile  Reply with Quote
as told before you cant select LISTID directly so far as you're grouping on Employee

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 05/05/2012 :  18:51:01  Show Profile  Reply with Quote
so how do i get it in there to use it?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/05/2012 :  18:59:14  Show Profile  Reply with Quote
quote:
Originally posted by cincyKid

so how do i get it in there to use it?


I cant understand what meaning does it make to show LISTID against data aggregated to employee level
Post some sample data and show what should be your output. unless you do that I cant make out why exactly you're doing this. Make sure you include all fields you want in output along with LISTID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 05/05/2012 :  19:25:32  Show Profile  Reply with Quote
ok lets try it:

Data Output:

Employee Name (EMPLDesc)
Total Companies (BLXF.BLXFBSNSID)
Earliest Date of activity (MinDate)
Latest Date of activity (MaxDate)
The List ID (LISTID)

so if you picture a table with columns and rows, each row will have the above information. The LISTID will come in play in my link. So something like:

<a href="details.asp?listID=<%=RS("LISTID")%>"><%=RS("MinDate")%></a>

Make sense?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/05/2012 :  21:38:20  Show Profile  Reply with Quote
see how to post information for question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

post like this and I will try to help
No point in going further with guess work!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 09/27/2012 :  11:51:59  Show Profile  Reply with Quote
ok, one more follow question on this one:

http://forums.devshed.com/showpost.php?p=2822936&postcount=26

calculating percenatge form 2 aggregate functions within a single query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  12:02:34  Show Profile  Reply with Quote
just try this and see if it gives correct values

SUM(CASE WHEN ad_info.approv = 1 THEN 1 END) / COUNT(ad_info.inv_number)) * 100.0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 09/27/2012 :  12:10:09  Show Profile  Reply with Quote
thanks visakh, someone answered in the other thread and I have it working, just trying to fine tune the number of digits and round it up, etc.

thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  12:31:15  Show Profile  Reply with Quote
welcome

the reason was implicit conversion!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000