Solved

Sum counts for all users even those without a count

Posted on 2013-11-21
13
298 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now