Page 2 of 2 First 12
  • Jump to page:
    #16
  1. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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!
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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?

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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?
  8. #20
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #21
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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.
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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!
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep 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:
    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
  20. #26
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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!
  22. #27
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  24. #28
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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")%> %
  26. #29
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep 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!
  28. #30
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    you're welcome
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo