Solved

MySQL select statement not showing correct sums

Posted on 2013-11-22
3
305 Views
Last Modified: 2013-11-30
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!
0
Comment
Question by:Sim1980
  • 2
3 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 

Accepted Solution

by:
Sim1980 earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:Sim1980
Comment Utility
Figured it out on my own.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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 video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

11 Experts available now in Live!

Get 1:1 Help Now