MySQL select statement not showing correct sums

Hi all.

I'm working on a select statement for a MySQL query.

I have 3 tables: Users, FieldSuperDataEntry, ShortageDataEntry

Users data (userid-firstname-lastname): 1-John Smith, 2-Jan Smith, 3-Ron Smith
FieldSuperDataEntry data (DataEntryID-Type-UserID-CreateDate):
1-1-1- 2013-11-09
2-2-1- 2013-11-19
3-2-1- 2013-11-20
4-1-2- 2013-11-21
ShortageDataEntry data (ShortageID-ShortageProduct-UserID-CreateDate):
1-ABC-1- 2013-11-18
2-DEF-1- 2013-11-19
3-GHI-3- 2013-11-22

How I need the output to appear:
Super---Slabs---DriedIns---Shortages
John Smith---0---2---1
Jan Smith---1---0---0
Ron Smith---0---0---1

When I use select statement 1 (it does not include shortages) it shows the correct data.
Select Statement 1:
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super,    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
   FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID AND (Date(FieldSuperDataEntry.CreateDate) >= '2013-11-19'AND Date(FieldSuperDataEntry.CreateDate) <= '2013-11-30')    Where Users.UserType = 'Super'
GROUP BY Super
ORDER BY Super

Open in new window


When I use Select Statement 2, then what happens is I get the correct data for slabs and dried in, but the shortages is incorrect, what it does is it sums the totals for slab and driedin and places that total in the shortages column for those users that have a shortage within the date parameter. For those users with no shortage data within the date parameters it shows 0 which is correct, but it's not showing the correct count for those that do have shortage data.
Select Statement 2:
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super,    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, SUM(CASE WHEN IFNULL(ShortageID,0)>0 THEN 1 ELSE 0 END) as Shortages
   FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID AND (Date(FieldSuperDataEntry.CreateDate) >= '2013-11-19'AND Date(FieldSuperDataEntry.CreateDate) <= '2013-11-30')   LEFT OUTER JOIN  ShortageDataEntry ON Users.userid = ShortageDataEntry.UserID AND (Date(ShortageDataEntry.CreateDate) >= '2013-11-19'AND Date(ShortageDataEntry.CreateDate) <= '2013-11-30')  Where Users.UserType = 'Super'
GROUP BY Super
ORDER BY Super

Open in new window


What am I doing wrong in the select statement?

Thank you in advance!
Sim1980Asked:
Who is Participating?
 
Sim1980Connect With a Mentor Author Commented:
Figured it out:

SELECT     Concat(firstname,' ', lastname) As Super, SUM(Slabs) AS Slabs, SUM(DriedIn) AS DriedIn, SUM(Shortages) AS Shortages, SUM(WorkSchedule) As WorkSchedule
FROM         (SELECT     Users.firstname, Users.lastname, SUM(CASE WHEN Type IS NULL OR
                                              Type <> 1 THEN 0 ELSE 1 END) AS Slabs, 0 AS DriedIn, 0 AS Shortages,0 As WorkSchedule
                       FROM      Users LEFT OUTER JOIN
                                              JobTypeHistory ON Users.userid = JobTypeHistory.UserID AND 
                                              JobTypeHistory.CreateDate >= :reportdate1 AND JobTypeHistory.CreateDate <= :reportdate2
                                              WHERE Users.usertype = 'Super'
                       GROUP BY Users.firstname, Users.lastname
                       UNION ALL
                       SELECT     Users.firstname, Users.lastname, 0 AS Slabs, SUM(CASE WHEN Type IS NULL OR
                                             Type <> 2 THEN 0 ELSE 1 END) AS DriedIn, 0 AS Shortages, 0 As WorkSchedule
                       FROM         Users LEFT OUTER JOIN
                                             JobTypeHistory ON Users.userid = JobTypeHistory.UserID AND 
                                             JobTypeHistory.CreateDate >= :reportdate3 AND JobTypeHistory.CreateDate <= :reportdate4
                                             WHERE Users.usertype = 'Super'
                       GROUP BY Users.firstname, Users.lastname
                       UNION ALL
                       SELECT     Users.firstname, Users.lastname, 0 AS Slabs, 0 AS DriedIn, SUM(CASE WHEN ShortageID IS NULL THEN 0 ELSE 1 END) AS Shortages, 0 As WorkSchedule
                                             
                       FROM         Users LEFT OUTER JOIN
                                             ShortageDataEntry ON Users.userid = ShortageDataEntry.UserID AND ShortageDataEntry.CreateDate >= :reportdate5 AND 
                                             ShortageDataEntry.CreateDate <= :reportdate6
                                             WHERE Users.usertype = 'Super'
                       GROUP BY Users.firstname, Users.lastname

                       UNION ALL
                       SELECT     Users.firstname, Users.lastname, 0 AS Slabs, 0 AS DriedIn, 0 As Shortages ,SUM(CASE WHEN WorkScheduleID IS NULL THEN 0 ELSE 1 END) As WorkSchedule
                                            
                       FROM         Users LEFT OUTER JOIN
                                             WorkScheduleDataEntry ON Users.userid = WorkScheduleDataEntry.UserID AND WorkScheduleDataEntry.CreateDate >= :reportdate7 AND 
                                             WorkScheduleDataEntry.CreateDate <= :reportdate8
                                             WHERE Users.usertype = 'Super'
                       GROUP BY Users.firstname, Users.lastname) x
GROUP BY  Concat(firstname,' ', lastname)
ORDER BY  Concat(firstname,' ', lastname)

Open in new window

0
 
SharathData EngineerCommented:
Can you post the result of this query and the expcted result?
SELECT Concat(Users.firstname, ' ', Users.lastname) As Super,  Type,ShortageID
   FROM Users  LEFT OUTER JOIN  FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID AND (Date(FieldSuperDataEntry.CreateDate) >= '2013-11-19'AND Date(FieldSuperDataEntry.CreateDate) <= '2013-11-30')   LEFT OUTER JOIN  ShortageDataEntry ON Users.userid = ShortageDataEntry.UserID AND (Date(ShortageDataEntry.CreateDate) >= '2013-11-19'AND Date(ShortageDataEntry.CreateDate) <= '2013-11-30')  Where Users.UserType = 'Super'
ORDER BY Super

Open in new window

0
 
Sim1980Author Commented:
Figured it out on my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.