Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query using multiple counts with conditions

Author  Topic 

cincyKid
Starting Member

22 Posts

Posted - 2012-04-13 : 19:21:05
Hey all,

I tried to post my question here:

[url]http://forums.devshed.com/ms-sql-development-95/sql-query-using-multiple-counts-with-where-clauses-and-inner-897256.html[/url]

But it doesn't seem like it has as much activity as this place does and even though someone is trying to help me there I dont know if we are understanding each other very well.

I was going to repost the question here, but there are multiple posts there that may help in answering my question.

any help is appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 20:59:38
sound like this

SQL = "SELECT EMPL.EMPLDesc, COUNT(BLXF.BLXFBSNSID) AS total,COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END) AS MulCnt,COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END) AS NonMulCnt" & _
"FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID " & _
"INNER JOIN BSST ON BLXF.BLXFBSSTID = BSST.BSSTID " & _
"INNER JOIN EMPL ON LIST.LISTEMPLID = EMPL.EMPLID " & _
"INNER JOIN PROJ ON LIST.LISTPROJID = PROJ.PROJID " & _
"WHERE (LIST.LISTAvailable = 1) AND (BSST.BSSTTerminalInd = 0) AND (PROJ.PROJPJSTID = 2) AND (EMPL.EMPLStatus = 'y') " & _ _
"GROUP BY EMPL.EMPLDesc " & _
"ORDER BY theNum DESC, EMPL.EMPLDesc"


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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-04-14 : 07:54:50
Thanks for the quick reply visakh16!

I have not tried this yet but will do so here soon. If that works, I will still not understand one thing.

I see where you count BLXF.BLXFBSNSID as the total, but on the other 2 counts that have the conditions, how does it know which column to count? I mean what if it counts EMPLID instead?

(just trying to understand the logic so I can learn from this instead of just having the answer) :)

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-04-14 : 08:25:01
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'BLXF'.

testapp.asp, line 21

something about that first count?


EDIT - Nevermind, I needed to put a space after the select and before the word from ;)

Now i am checking to see if the data its displaying is correct...stay tuned!
Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-04-14 : 08:49:39
well, at first glance, the data seems ot be correct and it is working!

I still don't understand how it knows which column to count in the other 2 aggregate functions but I really appreciate your help!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-15 : 13:10:20
for other two counts it looks at instances where condition given inside holds good and counts 1 for that ie it will give you count of occurances of the given condition as in LIST.LISTDESC LIKE '%-MUL%' or LIST.LISTDESC NOT LIKE '%-MUL%'. for other cases it will ignore the count

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 09:54:38
follow up question?

So now I want to add another column in the result set but I have tried a few different ways and it's not working for me. I need to add a column to show the time the row was last updated (which is BLXF.LastUpdate). But I actually want to find out in each list, when the latest date was and when the earliest date was.

So I thought about using more aggregate functions in the SQL:

MAX(BLXF.LastUpdate)
MIN(BLXF.LastUpdate)

However, there are 2 things that are holding me back.

1) I only want to calculate these Max and Min dates when the list is available (LIST.LISTAVAILABLE = 1)

2) I need to make sure that the rows are still lining up and giving me the data for each employee (EMPL.EMPLID) and for the specific project (PROJ.PROJID).

First attempt:


SQL = "SELECT EMPL.EMPLDesc, MAX(BLXF.LastUpdate) AS MaxDate, MIN(BLXF.LastUpdate) AS MinDate, COUNT(BLXF.BLXFBSNSID) AS total, " & _
"COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END) AS MulCnt, " & _
"COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END) AS NonMulCnt" & _
"FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID " & _
"INNER JOIN BSST ON BLXF.BLXFBSSTID = BSST.BSSTID " & _
"INNER JOIN EMPL ON LIST.LISTEMPLID = EMPL.EMPLID " & _
"INNER JOIN PROJ ON LIST.LISTPROJID = PROJ.PROJID " & _
"WHERE (LIST.LISTAvailable = 1) AND (BSST.BSSTTerminalInd = 0) AND (PROJ.PROJPJSTID = 2) AND (EMPL.EMPLStatus = 'y') " & _
"GROUP BY EMPL.EMPLDesc " & _
"ORDER BY theNum DESC, EMPL.EMPLDesc"


That gives me the max and min dates, but it does it from all lists and not just the ones that are available (LISTAVAILABLE = 1)



Second attempt:


SQL = "SELECT EMPL.EMPLDesc, " & _
"(SELECT MAX(BLXF.BLXFLastUpdate) FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID WHERE LIST.LISTAvailable = 1) AS theMaxDate, " & _
"(SELECT MIN(BLXF.BLXFLastUpdate) FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID WHERE LIST.LISTAvailable = 1) AS theMinDate, " & _
"COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END) AS MulCnt, " & _
"COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END) AS NonMulCnt" & _
"FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID " & _
"INNER JOIN BSST ON BLXF.BLXFBSSTID = BSST.BSSTID " & _
"INNER JOIN EMPL ON LIST.LISTEMPLID = EMPL.EMPLID " & _
"INNER JOIN PROJ ON LIST.LISTPROJID = PROJ.PROJID " & _
"WHERE (LIST.LISTAvailable = 1) AND (BSST.BSSTTerminalInd = 0) AND (PROJ.PROJPJSTID = 2) AND (EMPL.EMPLStatus = 'y') " & _
"GROUP BY EMPL.EMPLDesc " & _
"ORDER BY theNum DESC, EMPL.EMPLDesc"


That gives me the max and min dates, but its from any list in the system and does go by whats available and the specific project we are looking at (PROJ.PROJID).

I have tried making the subqueries more complex and joining many tables together but not only is it clunky but I ma still not getting the correct result set.

Anyone have any advice for me?

thanks again!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 10:41:45
try

SQL = "SELECT EMPL.EMPLDesc, MAX(CASE WHEN LIST.LISTAvailable = 1 THEN BLXF.LastUpdate END) AS MaxDate,
MIN(CASE WHEN LIST.LISTAvailable = 1 THEN BLXF.LastUpdate END) AS MinDate, COUNT(BLXF.BLXFBSNSID) AS total, " & _
"COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END) AS MulCnt, " & _
"COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END) AS NonMulCnt" & _
"FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID " & _
"INNER JOIN BSST ON BLXF.BLXFBSSTID = BSST.BSSTID " & _
"INNER JOIN EMPL ON LIST.LISTEMPLID = EMPL.EMPLID " & _
"INNER JOIN PROJ ON LIST.LISTPROJID = PROJ.PROJID " & _
"WHERE (LIST.LISTAvailable = 1) AND (BSST.BSSTTerminalInd = 0) AND (PROJ.PROJPJSTID = 2) AND (EMPL.EMPLStatus = 'y') " & _
"GROUP BY EMPL.EMPLDesc " & _
"ORDER BY theNum DESC, EMPL.EMPLDesc"


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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 11:48:33
wow man, it looks like it works!

thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 12:31:28
welcome

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 15:35:06
I have so many more questions about doing things with this query that I dont fully understand and that is causing me delays in working on it, but I guess one of the most frustrating ones is the whole GROUP BY thing.

Take the same query above. If I want to select another column such as LIST.LISTID so that I can use it later in html, it wont let me. if I add it to the top someplace like:


SQL = "SELECT EMPL.EMPLDesc, LIST.LISTID, MAX(CASE WHEN LIST.LISTAvailable = 1 THEN BLXF.LastUpdate END) AS MaxDate,
MIN(CASE WHEN LIST.LISTAvailable = 1 THEN BLXF.LastUpdate END) AS MinDate, COUNT(BLXF.BLXFBSNSID) AS total, " & _
"COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END) AS MulCnt, " & _
"COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END) AS NonMulCnt" & _
"FROM BLXF INNER JOIN LIST ON BLXF.BLXFLISTID = LIST.LISTID " & _
"INNER JOIN BSST ON BLXF.BLXFBSSTID = BSST.BSSTID " & _
"INNER JOIN EMPL ON LIST.LISTEMPLID = EMPL.EMPLID " & _
"INNER JOIN PROJ ON LIST.LISTPROJID = PROJ.PROJID " & _
"WHERE (LIST.LISTAvailable = 1) AND (BSST.BSSTTerminalInd = 0) AND (PROJ.PROJPJSTID = 2) AND (EMPL.EMPLStatus = 'y') " & _
"GROUP BY EMPL.EMPLDesc " & _
"ORDER BY theNum DESC, EMPL.EMPLDesc"


then I get an error saying that it either needs to be part of the aggregate function or in the GROUP BY clause. if I add it to the GROUP BY clause then it messes up my data and changes the way it's all displayed. Is this just how it is and there are limitations to select statements or is there a work around?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 15:38:49
if you want totals based on Employee then you're effectively merging several rows of employee with different values for LISTID so you cant show all of them. You have to apply some kind of aggregate to show any one of them like MIN(),MAX() etc which is exactly what error suggests. What is value that you would interested to return for an employee?

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 16:07:07
darn, I just realized that last date thingy is kind of working but its not getting the correct data I want. It gets the latest date fine (MAX), but on the earliest date, it is not getting the latest row for that data. So for example lets say you have this in the DB:

company1
- 5/4/2012
- 4/25/2012
- 4/12/2012
- 3/30/2012

company2
- 5/4/2012
- 5/2/2012
- 4/29/2012
- 4/15/2012
- 3/4/2012

company3
- 4/29/2012
- 4/15/2012
- 3/22/2012


When I use my current query it will get the following data:

Max: 5/52012 (perfect!)
Min: 3/4/2012 (wrong! this is the most minimum date indeed but I want only the last activity on that company, so instead the answer should be 4/29/2012 because it is the last activity for that company and its the earliest date)

How do I make this tweak?

thanks!
Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 16:10:44
quote:
Originally posted by visakh16

if you want totals based on Employee then you're effectively merging several rows of employee with different values for LISTID so you cant show all of them. You have to apply some kind of aggregate to show any one of them like MIN(),MAX() etc which is exactly what error suggests. What is value that you would interested to return for an employee?

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






Ahh, ok I understand that. Ultimately what it will be is those Max and Min dates will be links. So when you click on the date it will take you to that list (from the LISTID). So I was trying to pass that LISTID in the querystring of the date link.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 16:25:40
quote:
Originally posted by cincyKid

darn, I just realized that last date thingy is kind of working but its not getting the correct data I want. It gets the latest date fine (MAX), but on the earliest date, it is not getting the latest row for that data. So for example lets say you have this in the DB:

company1
- 5/4/2012
- 4/25/2012
- 4/12/2012
- 3/30/2012

company2
- 5/4/2012
- 5/2/2012
- 4/29/2012
- 4/15/2012
- 3/4/2012

company3
- 4/29/2012
- 4/15/2012
- 3/22/2012


When I use my current query it will get the following data:

Max: 5/52012 (perfect!)
Min: 3/4/2012 (wrong! this is the most minimum date indeed but I want only the last activity on that company, so instead the answer should be 4/29/2012 because it is the last activity for that company and its the earliest date)

How do I make this tweak?

thanks!


sorry you output explanation doesnt make sense based on data posted
you dont even have 5/5/2012 in sample data so i dont know how you got it in output
For min date you're currently applying grouping only on employee not on company so it will only return min date for employee group and not based on company

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 16:29:45
after further review, i think the date thing may be working but i have a couple of inconsistent data lines so i am investigating further.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 16:41:59
ok...make sure you post correct data so as help us to help you better!

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 17:30:33
yes it appears the date thing was working properly so forget that post :)

But I am back to trying to get the LISTID selected in my query.

both dates (Min and Max) will be links to that details page of that date. The way they will get there is by passing the LISTID through the querystring.

keeping in mind what you said previously, is it possible to do that?

(as far as that data I posted before i meant 5/4/2012 (not 5/5/2012))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 17:33:47
quote:
Originally posted by cincyKid

yes it appears the date thing was working properly so forget that post :)

But I am back to trying to get the LISTID selected in my query.

both dates (Min and Max) will be links to that details page of that date. The way they will get there is by passing the LISTID through the querystring.

keeping in mind what you said previously, is it possible to do that?

(as far as that data I posted before i meant 5/4/2012 (not 5/5/2012))


you've multiple companies for same employee?

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

Go to Top of Page

cincyKid
Starting Member

22 Posts

Posted - 2012-05-05 : 17:49:08
yes, many
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 17:52:31
for each company you need max value of listid?

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

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -