Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0

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


    Hi all,

    I am trying to write a query that involves multiple counts (with conditional where clauses) and also tables that are joinined with Inner Joins.

    I have looked at a few examples that use multiple counts with inner joins and some that have multiple counts with conditional where clauses but form only one table. I need to use all 3.

    First off, I have this query that works fine:

    Code:
    SQL = "SELECT EMPL.EMPLDesc, COUNT(BLXF.BLXFBSNSID) AS theNum " & _
    		  "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') " & _
    		  "AND (LIST.LISTID NOT IN (SELECT LISTID FROM LIST WHERE LISTDESC LIKE '%-MUL%')) " & _
    		  "GROUP BY EMPL.EMPLDesc " & _
    		  "ORDER BY theNum DESC, EMPL.EMPLDesc"
    It shows a list of employees and the number of contacts each of them have with some conditions thrown in like making sure the list is available and the project is turned on and that the title of their list does not contain the characters "-MUL" in it anywhere.

    What I want to do now is not only count the contacts that do not have the characters "-MUL" in the name, but also count the ones that do contain "-MUL" in the name as well as a total for both of them.

    So i need 3 numbers for each employee:
    1) Total contacts
    2) Contacts Without "-MUL"
    3) Contacts with "-MUL"

    Would I do something like this:

    Code:
    SQL = "Select empl.emplDesc, (Select Count (blxf.blxfbsnid) FROM all_of_my_joins_listed_here WHERE this_condition_is_true AS totalContacts), (Select Count (blxf.blxfbsnid) FROM all_of_my_joins_listed_here WHere this_condition_is_true AS MulContacts)
    
    etc....
    And have the joins and the where clauses in each select throughout the entire query and then still do the joins in the main select statement?

    As you can see I am kind of lost and looking for direction.

    Any help would be appreciated!

    thanks in advance,

    CK
  2. #2
  3. 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 i need 3 numbers for each employee:
    1) Total contacts
    2) Contacts Without "-MUL"
    3) Contacts with "-MUL"
    what column has this contact information?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    Sorry I should have better explained the tables:

    EMPL - table that houses employee information
    - EMPLID (pk)
    - EMPLDESC (name of employee)
    - EMPLStatus (y/n for still employed or not)

    PROJ - table that houses project information
    - PROJID (pk)
    - PROJSTID (open or closed, 2 = open)

    LIST - table that houses contact list information
    - LISTID (pk)
    - LISTPROJID (ID of project its associated with)
    - LISTEMPLID (employee the list is assigned to)
    - LISTAvailable (is list active? 1= yes, 0=no)
    - LISTDESC (name of contact list, like SALT-LAKE-22)

    BSNS - table that houses business status information
    - BSNSID (pk)
    - BSNSDesc (name of business)

    BSST - table that houses business status information
    - BSSTID (pk)
    - BSSTTerminalInd (contact status. 0 = still open)

    BLXF - table that joins information together
    - BLXFID(pk)
    - BLXFBSSTID (the list ID)
    - BLXFBSNSID (business contact ID)

    So i guess to answer your question its the BLXF table that houses the contact information.

    Please let me know if any of the above does not make sense or if you have further questions.

    thank you!
  6. #4
  7. 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 i guess to answer your question its the BLXF table that houses the contact information.
    okay, but which column?

    there are 3 columns that i can see, and all of them are an "id" of some type, so which one are we expecting will or will not have the MUL in it?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    the "-MUL" column would be the LISTDESC column in the LIST table.

    that is the names of the lists like:

    SALT-LAKE-22
    SALT-LAKE-23
    SALT-LAKE-MUL
    ORLANDO-MUL
    ORLANDO-5

    etc...
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    now i'm totally lost

    "What I want to do now is not only count the contacts that do not have the characters "-MUL" in the name, but also count the ones that do contain "-MUL" in the name as well as a total for both of them."

    "So i guess to answer your question its the BLXF table that houses the contact information."

    "the "-MUL" column would be the LISTDESC column in the LIST table"
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    Sorry for the confusion. I want to end up with something like this:

    Name --------- NO MUL-------MUL--------TOTAL
    John Smith --------750 ---------250 ---------1000
    Ed James --------- 680 --------- 0 ---------- 680
    Lucy Johnson ----- 895 ---------300 --------- 1195

    So I need a count for all 3 conditions

    1) NO MUL
    Code:
    (LIST.LISTID NOT IN (SELECT LISTID FROM LIST WHERE LISTDESC LIKE '%-MUL%')
    2) MUL
    Code:
    (LIST.LISTID IN (SELECT LISTID FROM LIST WHERE LISTDESC LIKE '%-MUL%')
    3) TOTAL
    Code:
    I guess just Count(*) or something similar
    BLXF is the table that has the relationships with the contacts so it connects each business contact (BLXFBSNSID) with an employee (EMPLID) and to what list that contact is on (BLXFLISTID)


    so each row has:

    BLXFID -- BLXFBSNSID -- BLXFEMPLID -- BLXFLISTID


    Now in the LIST table it has the names of the lists like:

    SALT-LAKE-22
    ORLANDO-MUL (notice the "-MUL" in the name which is what I am counting above)

    So I am counting the column BXLF.BXLFBSNSID, but I could just as easily count BXLF.BXLFID too as they are both unique.

    hopefully this makes better sense. If not please ask away

    thanks again!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    i am still hopelessly lost

    i've re-written your query to make it a bit easier to read --
    Code:
    SELECT empl.empldesc
         , COUNT(blxf.blxfbsnsid) AS thenum
      FROM list
    INNER 
      JOIN blxf 
        ON blxf.blxflistid = list.listid 
    INNER 
      JOIN bsst 
        ON bsst.bsstid = blxf.blxfbsstid
       AND bsst.bsstterminalind = 0
    INNER 
      JOIN empl 
        ON empl.emplid = list.listemplid
       AND empl.emplstatus = 'y'
    INNER 
      JOIN proj 
        ON proj.projid = list.listprojid
       AND proj.projpjstid = 2
     WHERE list.listavailable = 1
       AND list.listid NOT IN 
           ( SELECT listid 
               FROM list 
              WHERE listdesc LIKE '%-MUL%') 
    GROUP 
        BY empl.empldesc 
    ORDER 
        BY thenum DESC
         , empl.empldesc
    the part in red appears to me to be counter-productive in counting employees with MUL

    two of the joins make no sense to me at all, as they appear unnecessary...

    ... but i guess i still don't understand all the table relationships

    as far as partial counts are concerned, this is the general strategy --
    Code:
    SELECT COUNT(*) AS total
         , COUNT(CASE WHEN something='foo' THEN 'ok' END) AS foo_count
         , COUNT(CASE WHEN something='bar' THEN 'ok' END) AS bar_count
    i wish you good luck in solving your problem
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    thanks for looking at it, I really appreciate it. The person who designed the database and wrote all of the core files is long gone and I am trying to poke through and fix some things and/or add functionality.

    The part in red exists if I wanted to count the number of businesses that are in lists whose list names do not include the characters "-MUL"

    as far as the joins:

    BXLF has all of the relationships between businessID, EmployeeID, and ListID

    LIST i need because I need to make sure that the list is available and also the listDesc does not contain "-MUL"

    BSST is the business status and I need to join this table because I am making sure the status = 0 meaning the contact is still active

    EMPL i need to get employee names and IDs

    PROJ I need the to see if the project is active


    Thanks for the direction on the partial counts although I wil have to do more research on them so I can understand their structure. I understand the conditional
    Code:
    WHEN something='foo'
    But i don't understand what the THEN is supposed to be or do
    Code:
    THEN 'ok'

    I imagine once I understand that, I can just continue on with my joins just like normal?
  18. #10
  19. 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 don't understand what the THEN is supposed to be or do
    Code:
    THEN 'ok'
    'ok' is a non-null value

    aggregate functions, including COUNT(), ignore null values

    CASE structure defaults the ELSE condition to NULL

    put all these facts together and you will understand how COUNT(CASE...END) works

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

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    so maybe it would be something like:

    Code:
    COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 ELSE 0 END) AS foo_count
    that way if its true it counts it? otherwise its a 0 (doesn't count it?)

    Actually i guess that wont work either because I need to tell it to count the column BXLF.BXLFBSNSID in there somehow. I need to figure out how that fits in with the case statement
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by ck2012
    otherwise its a 0 (doesn't count it?)
    close, but not quite

    you stuck ELSE 0 in there

    so you're overriding the default NULL

    sadly for you, 0 is not null

    therefore it counts it!!!

    if you had left the ELSE out, it wouldn't've counted it

    Originally Posted by ck2012
    ... count the column BXLF.BXLFBSNSID in there somehow.
    Code:
    SELECT COUNT(*) AS total
         , COUNT(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 'humpty' END) AS like_mul
         , COUNT(CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 'dumpty' END) AS not_like_mul
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    hehe, dang man I may more confused that you are

    first off, what does humpty or dumpty do for me? I mean if I am counting records, what should those values be?

    secondly, in your example, it's not counting the actual column I need (BXLF.BXLFBSNSID).

    I know this is wrong syntax but shouldn't it be something more like this:


    Code:
    SELECT COUNT(BXLF.BXLF.BXLFBSNSID) AS total
         , COUNT(BXLF.BXLF.BXLFBSNSID(CASE WHEN LIST.LISTDESC LIKE '%-MUL%' THEN 1 END)) AS like_mul
         , COUNT(BXLF.BXLF.BXLFBSNSID((CASE WHEN LIST.LISTDESC NOT LIKE '%-MUL%' THEN 1 END)) AS not_like_mul
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    'humpty' and 'dumpty' are non-null values

    as long as what you're doing is counting, it doesn't matter what it is

    if you count how many humpties are in a set of three humpties, there are three of them

    if you count how many bxlfbsnids are in a set of three bxlfbsnids, there are three of them

    three = three

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

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0
    but how do i use those variables? (humpty and dumpty).

    Also, still not sure how to incorporate the BLXFBSNSID column that i need to count.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo