Solved

MySQL select statement not showing correct sums

Posted on 2013-11-22
3
306 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
ID: 39670705
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
ID: 39675257
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
ID: 39686664
Figured it out on my own.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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