Solved

Sum counts for all users even those without a count

Posted on 2013-11-21
13
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 25

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 25

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 25

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 25

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 25

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 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