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

Dev Shed Forums Sponsor:

April 10th, 2012, 10:04 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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

April 10th, 2012, 04:01 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

Quote:

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

April 10th, 2012, 05:21 PM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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!

April 10th, 2012, 10:18 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

Quote:

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?

April 11th, 2012, 07:04 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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...

April 11th, 2012, 07:37 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

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"

April 11th, 2012, 11:09 AM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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!

April 12th, 2012, 08:43 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

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

April 12th, 2012, 10:48 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 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?

#10

April 12th, 2012, 11:06 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

Quote:

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

#11

April 12th, 2012, 12:53 PM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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

#12

April 12th, 2012, 01:45 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

Quote:

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

Quote:

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

#13

April 12th, 2012, 07:00 PM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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

#14

April 12th, 2012, 07:53 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m 19 sec
Reputation Power: 4140

'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

#15

April 13th, 2012, 05:38 PM

ck2012

Registered User

Join Date: Apr 2012

Posts: 15

Time spent in forums: 2 h 6 m 34 sec
Reputation 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.

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

Developer Shed Advertisers and Affiliates