Page 2 - SQL Query using multiple counts with where clauses and inner joined tables

Dev Shed Forums Sponsor:

#16

April 13th, 2012, 06:27 PM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

Originally Posted by ck2012

but how do i use those variables? (humpty and dumpty).

they aren't variables, they are non-null constants

did you understand my point about three being equal to three?

okay, so let's count something else instead

Code:

SELECT COUNT(*) AS total
     , COUNT(CASE WHEN list.listdesc LIKE '%-MUL%' 
                  THEN bxlf.bxlfbsnsid END) AS like_mul
     , COUNT(CASE WHEN list.listdesc NOT LIKE '%-MUL%' 
                  THEN bxlf.bxlfbsnsid END) AS not_like_mul

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

#17

April 14th, 2012, 07:52 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

thanks for your help r937!

I also posted this in another forum and the person told me pretty much the same syntax as you but put all the code together for me and you were both right, it works!

Code:

SQL = "SELECT EMPL.EMPLDesc, COUNT(BLXF.BLXFBSNSID) AS theNum, 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"

I don't understand how it knows which column to count in the other 2 counts with the case statements but I am happy that it's getting the values I intended.

thanks again!

#18

April 14th, 2012, 08:09 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

Originally Posted by ck2012

I don't understand how it knows which column to count in the other 2 counts with the case statements ...

once again, let me state that it's counting non-null values

could you do an experiment please?

replace your query with this one and tell me whether the results are the same...

Code:

SQL = "SELECT EMPL.EMPLDesc, COUNT(BLXF.BLXFBSNSID) AS theNum, COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 'humpty' END) AS MulCnt, COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 'dumpty' 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"

also, you forgot to answer my question -- did you understand my point about three being equal to three?

#19

April 14th, 2012, 08:21 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

LOL, it works too!

Ok i understand then that it doesn't matter what that non-null value is set to. And yes I understand what you meant about 3=3.

But, do you see my confusion? How does this:

Code:

COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 'humpty' END) AS MulCnt

know to count BLXF.BLXFBSNSID and not EMPL.EMPLID?

#20

April 14th, 2012, 01:18 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

Originally Posted by ck2012

How does this: ... know to count BLXF.BLXFBSNSID and not EMPL.EMPLID?

it doesn't, because you've told it to count 'humpty', not EMPL.EMPLID, and not BLXF.BLXFBSNSID

at it ~still~ produces the right count, lol omg

#21

April 14th, 2012, 03:25 PM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

So you aren't sure why either?

I try to learn why something works the way it does to help me in the future. My only guess is that when i mention the column to count as the total, it just uses the same column to count...

heck i don't know.

#22

April 14th, 2012, 03:36 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

Originally Posted by ck2012

So you aren't sure why either?

of course i am sure

i know exactly what's going on

the only thing that is frustrating me is that i am having a very difficult time explaining it in a way that you will understand

do me a favour, would you count the number of non-null values in each of these two columns --

Code:

BLXF.BLXFBSSTID    'humpty'
    34             'humpty'
    52             'humpty'
     9             'humpty'
    37             'humpty'
    88             'humpty'
    25             'humpty'
---------------    --------    
count=?            count=?

what counts do you get?

#23

May 5th, 2012, 08:56 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

Ok, I understand that, thank you.

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:

Code:

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:

Code:

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!

#24

May 6th, 2012, 06:22 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

Originally Posted by ck2012

But I actually want to find out in each list, when the latest date was and when the earliest date was.

that would require a GROUP BY on the list

you current query does a GROUP BY on the employee

see the difference?

#25

May 8th, 2012, 07:59 AM

Zarium

Registered User

Join Date: May 2012

Posts: 2

Time spent in forums: 41 m 39 sec
Reputation Power: 0

You need to restrict you subselects to your current listid. Joining doesn't work because BLXF in your outer select and BLXF in your inner selects are three separate things.
Try this:

sql Code:

Original - sql Code

SELECT EMPL.EMPLDesc,
(SELECT MAX(BLXF.BLXFLastUpdate) FROM LIST WHERE BLXF.BLXFLISTID = LIST.LISTID AND LIST.LISTAvailable = 1) AS theMaxDate, 
(SELECT MIN(BLXF.BLXFLastUpdate) FROM LIST WHERE BLXF.BLXFLISTID = LIST.LISTID AND 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

#26

September 27th, 2012, 10:45 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

follow up question!

How would I figure out a percentage of 2 separate aggregate functions in my query? So for example:

"SELECT EMPL.EMPLDesc, " & _
"COUNT(ad_info.inv_number) AS theTotal, " & _
"COUNT(CASE WHEN ad_info.approv = 1 THEN 1 END) AS theTotalApproved, " & _
"(SUM(CASE WHEN ad_info.approv = 1 THEN 1 END) / COUNT(ad_info.inv_number)) * 100 AS yourPercentage " & _

the above is the first part of my query. Basically I use 2 different Counts to get 2 separate numbers in my results which works fine. Now I need to divide the 2 numbers to figure out the percentage. I can use vbscript's FormatPercent function after the results are displayed but I need to order by the percentage so I can see who has the highest and who has the lowest very easily.

The line in red is what I need help with and the above line only gives me "0" for each row.

Can this be done? Am i on the right track?

thanks again!

#27

September 27th, 2012, 10:54 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

you're getting 0 because it's doing integer arithmetic

any number divided by a larger number will yield an answer that is less than 1, and integer arithmetic uses the equivalent of a FLOOR function

the trick is to multiply by 100.0 first, thus implicitly casting the entire expression into decimal

change the line in red as follows --

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

#28

September 27th, 2012, 11:08 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

wow, awesome!

Thanks again man, it always seems to be something small that causes the problem....

Now it shows 58.912912912912

I would like the end result to be just: 58%

I tried using Round in front of the line but it tells me it needs 2-3 arguments. Should I use CAST?

I guess I can always add the "%" after the results are displayed like:

Percent = <%=RS("yourPercetage")%> %

#29

September 27th, 2012, 11:12 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation Power: 0

got it now!

I just had to add the decimals to use.

so the line is now:

ROUND(100.0 * COUNT(CASE WHEN ad_info.approv = 1 THEN 1 END) / COUNT(ad_info.inv_number), 0) AS yourPercentage

thanks again for the quick reply!

#30

September 27th, 2012, 11:16 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

you're welcome

Page 2 - SQL Query using multiple counts with where clauses and inner joined tables

Developer Shed Advertisers and Affiliates