Solved

Sum counts for all users even those without a count

Posted on 2013-11-21
13
303 Views
Last Modified: 2013-11-21
Hi all.

I have the following select statement for a MySQL database. This displays the counts for slabs and driedins for Users that have entered data in the table FieldSuperDataEntry.

I want to also show Users that have NOT entered data in the FieldSuperDataEntry table in the date range in the Where clause, they should have 0 for slabs and driedins. Any idea how I can change my select statement?

Thank you in advance.

SELECT Concat(Users.firstname, ' ', Users.lastname) As Super, 
   SUM(CASE WHEN Type=1 THEN 1 ELSE 0 END) as Slabs, 
   SUM(CASE WHEN Type=2 THEN 1 ELSE 0 END) as DriedIn
FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID  LEFT OUTER JOIN JobType ON FieldSuperDataEntry.Type = JobType.jobtypeid WHERE (Date(CreateDate) >= '2013-11-01'AND Date(CreateDate) <= '2013-11-30') 
GROUP BY Super
ORDER BY Super

Open in new window

0
Comment
Question by:Sim1980
  • 6
  • 5
  • 2
13 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39667409
You have been right to use LEFT OUTER JOIN. In case the users did not enter FieldSuperDataEntry I think the value Type will be NULL. So the statement should look something like this:
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super, 
   SUM(CASE WHEN Type IS NULL or Type<>1 THEN 0 ELSE 1 END) as Slabs, 
   SUM(CASE WHEN Type IS NULL or Type<>2 THEN 0 ELSE 1 END) as DriedIn
FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID  LEFT OUTER JOIN JobType ON FieldSuperDataEntry.Type = JobType.jobtypeid WHERE (Date(CreateDate) >= '2013-11-01'AND Date(CreateDate) <= '2013-11-30') 
GROUP BY Super
ORDER BY Super

Open in new window

OR you can use ISNULL(), like this
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super, 
   SUM(CASE WHEN ISNULL(Type,0)=1 THEN 1 ELSE 0 END) as Slabs, 
   SUM(CASE WHEN ISNULL(Type,0)=2 THEN 1 ELSE 0 END) as DriedIn
FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID  LEFT OUTER JOIN JobType ON FieldSuperDataEntry.Type = JobType.jobtypeid WHERE (Date(CreateDate) >= '2013-11-01'AND Date(CreateDate) <= '2013-11-30') 
GROUP BY Super
ORDER BY Super

Open in new window

0
 
LVL 1

Expert Comment

by:Dephault
ID: 39667422
Check for the null values

SUM(CASE WHEN ISNULL([Type],0)=1 THEN 1 ELSE 0 END) as Slabs, 
SUM(CASE WHEN ISNULL([Type],0)=2 THEN 1 ELSE 0 END) as DriedIn

Open in new window

0
 

Author Comment

by:Sim1980
ID: 39667423
Thank you for the reply.

The first select statement only showed me users that entered data in the table within the date parameter, it didn't show the other users with 0 for slabs and driedins.

The second select statement gave me an error in MySQL, near the ISNULL(Type,0)=1 part of the code.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:Sim1980
ID: 39667432
Dephault, I got an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

 [Type],0)=1 THEN 1 ELSE 0 END) as Slabs,
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39667450
Move date criteria to your ON clause:
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super, 
   SUM(CASE WHEN Type IS NULL or Type<>1 THEN 0 ELSE 1 END) as Slabs, 
   SUM(CASE WHEN Type IS NULL or Type<>2 THEN 0 ELSE 1 END) as DriedIn
FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID  LEFT OUTER JOIN JobType ON FieldSuperDataEntry.Type = JobType.jobtypeid AND (Date(CreateDate) >= '2013-11-01'AND Date(CreateDate) <= '2013-11-30') 
GROUP BY Super
ORDER BY Super

Open in new window

BTW, I think MySQL uses IFNULL. Why did you tagged your question as MS SQL Server?
0
 

Author Closing Comment

by:Sim1980
ID: 39667460
That was it. Thanks!
0
 

Author Comment

by:Sim1980
ID: 39667476
Actually, chaau, it doesn't look at the data parameter, is sums counts for other dates outside of the parameter.
0
 
LVL 1

Expert Comment

by:Dephault
ID: 39667478
Sorry my answer was SQL Server. This should be the MySQL syntax. (but Im too late -its already answered)
SUM(CASE WHEN IFNULL(`Type`,0)=1 THEN 1 ELSE 0 END) as Slabs, 
SUM(CASE WHEN IFNULL(`Type`,0)=2 THEN 1 ELSE 0 END) as DriedIn

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 39667522
@Sim1980, wasn't it what you was after?
0
 

Author Comment

by:Sim1980
ID: 39667538
I wanted it to sum the count that falls within the date parameter, putting the date parameter in the ON portion ignores the date parameter.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39667552
Then move it back to WHERE clause.
Why did you say this before?
The first select statement only showed me users that entered data in the table within the date parameter, it didn't show the other users with 0 for slabs and driedins.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39667583
OK, I figured. I probably put the date criteria into a wrong ON clause. Try this:
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super, 
   SUM(CASE WHEN Type IS NULL or Type<>1 THEN 0 ELSE 1 END) as Slabs, 
   SUM(CASE WHEN Type IS NULL or Type<>2 THEN 0 ELSE 1 END) as DriedIn
FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID AND (Date(CreateDate) >= '2013-11-01'AND Date(CreateDate) <= '2013-11-30') LEFT OUTER JOIN JobType ON FieldSuperDataEntry.Type = JobType.jobtypeid 
GROUP BY Super
ORDER BY Super

Open in new window

Please, to make our lives easier, try to use aliases. It is very difficult to figure out what table your columns belong to. The query above assumes that CreateDate belongs to the table FieldSuperDataEntry
0
 

Author Comment

by:Sim1980
ID: 39667748
Thank you chaau! That did it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question