asked on

Sum counts for all users even those without a count

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

chaau

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

Dephault

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

Sim1980

ASKER

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.

Sim1980

ASKER

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,

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sim1980

ASKER

That was it. Thanks!

Sim1980

ASKER

Actually, chaau, it doesn't look at the data parameter, is sums counts for other dates outside of the parameter.

Dephault

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

chaau

@Sim1980, wasn't it what you was after?

Sim1980

ASKER

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.

chaau

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.

chaau

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

Sim1980

ASKER

Thank you chaau! That did it.