Solved

MySQL select statement not showing correct sums

Posted on 2013-11-22
3
307 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

786 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