Solved

MySQL select statement not showing correct sums

Posted on 2013-11-22
3
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 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