How do I make count show 0s?

rhaas128

Board Regular
Joined
Jul 5, 2013
Messages
84
Below is the sql query that I am running. Currently, if a user has 0 open for the number of tickets, their name is removed from my list. I need them to show with a 0. Otherwise, I do not realize they have no work. Thoughts?

I am relatively new to this so any and all help in lamens terms is greatly appreciated!

PHP:
SELECT Count(dbo_Transactions.TransNbr) AS [# of Tickets], dbo_Transactions.TransStatus AS Status, dbo_Users.FirstName, dbo_Users.LastName
FROM dbo_Users INNER JOIN dbo_Transactions ON dbo_Users.SSN = dbo_Transactions.SecID
GROUP BY dbo_Transactions.TransStatus, dbo_Users.FirstName, dbo_Users.LastName, dbo_Users.FirstName
HAVING (((dbo_Transactions.TransStatus)="Open") AND ((dbo_Users.LastName)="last1" Or (dbo_Users.LastName)="last2" Or (dbo_Users.LastName)="last3" Or (dbo_Users.LastName)="last4" Or (dbo_Users.LastName)="last5"));
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
If you do a LEFT JOIN, you can return ALL the records from a certain table, regardless of whether or not they have any matching records in the other table you are joining to, i.e.
Code:
[COLOR=#0000BB][FONT=monospace]SELECT Count[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Transactions[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]TransNbr[/FONT][/COLOR][COLOR=#007700][FONT=monospace]) AS [[/FONT][/COLOR][COLOR=#FF8000][FONT=monospace]# of Tickets], dbo_Transactions.TransStatus AS Status, dbo_Users.FirstName, dbo_Users.LastName
[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]FROM dbo_Users [B]LEFT[/B] JOIN dbo_Transactions ON dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]SSN [/FONT][/COLOR][COLOR=#007700][FONT=monospace]= [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Transactions[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]SecID
GROUP BY dbo_Transactions[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]TransStatus[/FONT][/COLOR][COLOR=#007700][FONT=monospace], [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]FirstName[/FONT][/COLOR][COLOR=#007700][FONT=monospace], [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace], [/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]FirstName
HAVING [/FONT][/COLOR][COLOR=#007700][FONT=monospace]((([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Transactions[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]TransStatus[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"Open"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]) AND (([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"last1" [/FONT][/COLOR][COLOR=#007700][FONT=monospace]Or ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"last2" [/FONT][/COLOR][COLOR=#007700][FONT=monospace]Or ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"last3" [/FONT][/COLOR][COLOR=#007700][FONT=monospace]Or ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"last4" [/FONT][/COLOR][COLOR=#007700][FONT=monospace]Or ([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Users[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]LastName[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"last5"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]));  [/FONT][/COLOR]
 
Upvote 0
When I change to LEFT join, it doesn't give me any different result. I still do not see the users that have 0 open tickets.
 
Upvote 0
That's probably because of your criteria. Try removing the part of your criteria that is referencing fields in this table with no matching records for some people, specifically, this part:
Code:
[COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]dbo_Transactions[/FONT][/COLOR][COLOR=#007700][FONT=monospace].[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]TransStatus[/FONT][/COLOR][COLOR=#007700][FONT=monospace])=[/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"Open"[/FONT][/COLOR][COLOR=#007700][FONT=monospace])[/FONT][/COLOR]
If there are no records for a person, obviously that field cannot be "Open" so they would be dropped.

If you need to exclude those records from consideration, move that criteria up to the WHERE clause of your code (you can have both WHERE and HAVING clauses - WHERE happens before the HAVING part does).
 
Upvote 0
I did not get the result I was looking for. Could you put the code exactly how it should be? Perhaps I am doing it incorrectly.
 
Upvote 0
Actually, it looks like we have to reduce the Transactions table down first in a sub-query in order to get it to work properly.
So if my assumptions are correct, then this should work:
Code:
[COLOR=#0000BB]SELECT Count[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Trans[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]TransNbr[/COLOR][COLOR=#007700]) AS [[/COLOR][COLOR=#FF8000]# of Tickets], Trans.TransStatus AS Status, dbo_Users.FirstName, dbo_Users.LastName
[/COLOR][COLOR=#0000BB]FROM dbo_Users 
[B]LEFT[/B] JOIN 
[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]SELECT dbo_Transactions.* 
 FROM [/COLOR][COLOR=#0000BB]dbo_Transactions
[/COLOR][COLOR=#0000BB] WHERE [/COLOR][COLOR=#0000BB]dbo_Transactions[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]TransStatus[/COLOR][COLOR=#007700]=[/COLOR][COLOR=#DD0000]"Open"[/COLOR][COLOR=#007700]) as Trans[/COLOR][COLOR=#0000BB]
ON dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]SSN [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Trans[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]SecID
GROUP BY Trans[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]TransStatus[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]FirstName[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]FirstName
HAVING [/COLOR][COLOR=#007700]([/COLOR][COLOR=#007700](([/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700])=[/COLOR][COLOR=#DD0000]"last1" [/COLOR][COLOR=#007700]Or ([/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700])=[/COLOR][COLOR=#DD0000]"last2" [/COLOR][COLOR=#007700]Or ([/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700])=[/COLOR][COLOR=#DD0000]"last3" [/COLOR][COLOR=#007700]Or ([/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700])=[/COLOR][COLOR=#DD0000]"last4" [/COLOR][COLOR=#007700]Or ([/COLOR][COLOR=#0000BB]dbo_Users[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]LastName[/COLOR][COLOR=#007700])=[/COLOR][COLOR=#DD0000]"last5"[/COLOR][COLOR=#007700]));  [/COLOR]
If my assumptions are not correct, please provide sample data and the logic behind your criteria (what it is supposed to be doing).
 
Upvote 0
Can you advise how I could include "pending" tickets as well in the same query? So it shows both open and pending and distinguishes betwee the two?
 
Upvote 0
This gets a little trickier. A few changes you need to make:
- Have your criteria return both Open and Pending items.
- Change your calculated fields so that it return a 1 or 0 if it is Open, and then Sum those values (and repeat for Pending).
- Remove "Status" from returned field and Grouping.

So it would look something like this:
Code:
SELECT Sum(IIF(Trans.TransStatus="Open",1,0)) as OpenCount, Sum(IIF(Trans.TransStatus="Pending",1,0)) as PendingCount, dbo_Users.FirstName, dbo_Users.LastName
FROM dbo_Users 
LEFT JOIN 
(SELECT dbo_Transactions.* 
 FROM dbo_Transactions
 WHERE dbo_Transactions.TransStatus in ("Open","Pending")) as Trans
ON dbo_Users.SSN = Trans.SecID
GROUP BY dbo_Users.FirstName, dbo_Users.LastName, dbo_Users.FirstName
HAVING (((dbo_Users.LastName)="last1" Or (dbo_Users.LastName)="last2" Or (dbo_Users.LastName)="last3" Or (dbo_Users.LastName)="last4" Or (dbo_Users.LastName)="last5"));
 
Upvote 0
Again, this works fabulously and I greatly appreciate it! I do have 1 other question. Is it possible to make all open and pending show in a single column? As it stands now, open show i one and pending show in the other. That would be all I need to complete this.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top