[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Syntax - Max, Sum

Posted on 2014-08-12
9
Medium Priority
?
162 Views
Last Modified: 2014-11-17
The query below returns an incorrect value for the column 'Cost JTD' (line 12). It returns two rows. The first row the column is 430,251 and it should be 185,367. The second row returns the correct value for that column since it only has one value. The first row has several data points that are added and obviously the wrong ones are grouped in the SUM. Look for /* this it the query in question */ in the below query

#Field Production Report (MATERIALS, ORIGINAL ESTIMATE/AS BID, JOB TO DATE, COST PROJECTION)
/*01*/SELECT
/*02*/t.SCTYPE 'Desc',
/*03*/t.UM 'UoM',
/*04*/FORMAT(SUM(c.POPU), 0) 'Orig Qty',
/*05*/IF(t.UM = 'LS', 1, ROUND(sf_PcacAvgPR(c.JOB_ID, t.SCTYPE), 0)) 'Orig PR',
/*06*/c.POEST 'Orig HR',
/*07*/FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours',
/*08*/FORMAT(SUM(((c.POPU / c.POUEST) * c.POEST)), 0) 'Est Cost',
/*09*/ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost',
/*10*/FORMAT(Placed_JTD, 0) 'Placed JTD',
/*11*/FORMAT(Hours_JTD, 0) 'Hours_JTD',
/*12*/CONCAT('$', FORMAT(SUM(CostJTD), 0)) 'Cost JTD',
/*13*/IF(t.UM = 'LS', 1, FORMAT(Placed_JTD/Hours_JTD, 2)) 'Actual PR',
/*14*/FORMAT(CostJTD/Placed_JTD, 4) 'Unit Cost JTD',      
/*15*/CONCAT('$', FORMAT((c.POEST / c.POUEST) * Placed_JTD, 0)) 'Est Cost',      
/*16*/CONCAT('$', FORMAT((c.POEST / c.POUEST) * Placed_JTD - SUM(CostJTD), 0)) 'Variance',
/*17*/FORMAT(SUM(CostJTD)/Hours_JTD, 2) 'Avg HR'
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT s.SCTYPE PLACED, SUM(m.PLACEMENT) Placed_JTD
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7588
   AND m.DATE_PLACE <= '2014-08-03'
   AND m.DELETED = 'N'
   AND c.POPU > 0
GROUP BY s.SCTYPE) sub_query1 ON s.SCTYPE = sub_query1.PLACED

/* this it the query in question */
LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
   FROM jcost o
   LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   WHERE o.JOB_ID = 7588  
   AND o.DELETED = 'N'
   AND o.WEDATE <= '2014-08-03'
GROUP BY s.SCTYPE) sub_query2 ON s.SCTYPE = sub_query2.COST
/* this it the query in question */

LEFT JOIN (SELECT s.SCTYPE HOURS, SUM(l.HOURS) Hours_JTD
   FROM labor l
   INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   INNER JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7588
   AND l.DATE_WORK <= '2014-08-03'
   AND l.DELETED = 'N'
GROUP BY s.SCTYPE) sub_query4 ON s.SCTYPE = sub_query4.HOURS
LEFT JOIN (SELECT s.SCTYPE LS, SUM(ls.LUMPSUM) LS_Updated_Variance
   FROM pcacls ls
   JOIN ccode c ON c.CCODE_ID = ls.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7588
   AND ls.DELETED = 'N'
   AND c.POPU > 0
GROUP BY s.SCTYPE) sub_query5 ON s.SCTYPE = sub_query5.LS
 LEFT JOIN (SELECT s.SCTYPE RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   LEFT JOIN
   (SELECT s.SCTYPE RATE, MAX(PCACPR_ID) Last_PCACPR_ID
   FROM pcacpr pr LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-08-03'
   GROUP BY s.SCTYPE) Last_pr ON s.SCTYPE = Last_pr.RATE AND pr.PCACPR_ID = Last_pr.Last_PCACPR_ID
   AND j.JOB_ID = 7588
GROUP BY s.SCTYPE) sub_query6 ON s.SCTYPE = sub_query6.RATE
WHERE j.JOB_ID = 7588
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;

The query below returns the proper values to be summed. However there are problems since if you replace it with the existing query (which is only different by the GROUP BY using o.SCCODE instead of s.SCTYPE), it affects other numbers in the query? But even if it is used it only shows the total of both rows not each one separately.

SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
LEFT JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7588            
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE;

  13683.75
  24891.28
  38862.48
107562.82
       366.20
____________
185,366.60
0
Comment
Question by:hdcowboyaz
  • 5
  • 2
  • 2
9 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 40257092
Can you please simplify your query? Don't you have an easier snipped that reproduce the issue?
0
 

Author Comment

by:hdcowboyaz
ID: 40257122
Yes I can. I simplified it the first time and thought it solved my problem but when I put it back in the full query it broke other things. This is the simplified verison. If I change the GROUP BY s.SCTYPE to GROUP BY o.SCCODE I will get correct values. However, it will mess up other columns of data and it will only return the total of both rows not each one separately.

SELECT SUM(CostJTD)
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
   FROM jcost o
   LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   WHERE o.JOB_ID = 7588            
   AND o.DELETED = 'N'
   AND o.WEDATE <= '2014-08-03'
GROUP BY s.SCTYPE) sub_query2 ON s.SCTYPE = sub_query2.COST
WHERE j.JOB_ID = 7588
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;
0
 
LVL 15

Expert Comment

by:gplana
ID: 40257148
I think you want a sum of a column for some grouping data, and a sum of another column for some other grouping data. This is not possible to do it with just a query. You need two different queries for this, every of them with its group by.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:hdcowboyaz
ID: 40257173
I got a believe there is something you can do with in the query to get this data.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1500 total points
ID: 40257315
Please post the CREATE TABLE statements for these tables.  And please post the test data that can be loaded into these tables and that is needed to illustrate the problem.  Together, these are the SSCCE, an incredibly important concept in software development, and they are the information we need to help you get a solution.  Once we have your starting point and a clear understanding of what you want for the outputs we can almost always help!
0
 

Author Comment

by:hdcowboyaz
ID: 40257504
I'll do you one better. Attached is an ERD as well as an sqldump. The JOB_ID we will use is 7398.

This query results in a column of seven rows of data.
You add the first five SCTYPE of 1 to get 63,215.75
You add the last two SCTYPE of 3 to get 21,673.43
See below. These are the correct values you should get from the main query below. The main query return only two rows of data since its grouping (SUM(CostJTD) the first five SCTYPE of 1 and the last two SCTYPE of 3

SELECT s.SCTYPE, MAX(o.JTDCOST) CostJTD
FROM jcost o
INNER JOIN sccode s ON s.SCCODE = o.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7398      
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE;

14,817.43
  1,367.18
  4,147.51
41,925.46
     958.17
_________
63,215.75

20,989.65
     683.78
_________
21,673.43

Main Query but instead you get 125,857.3820,989.65

SELECT SUM(CostJTD)
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
   FROM jcost o
   LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   WHERE o.JOB_ID = 7398            
   AND o.DELETED = 'N'
   AND o.WEDATE <= '2014-08-03'
GROUP BY s.SCTYPE) sub_query2 ON s.SCTYPE = sub_query2.COST
WHERE j.JOB_ID = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;

Now I know what you are going to say. What if you used GROUP BY o.SCCODE in the last line above. You get 189,728.25 and 21,628.43. Not only are they wrong but the mess up several other values in the report.

Simply put the first query returns the correct values, Summing them is another story.
ERD-JDS.jpg
jds-s-8-12-14.sql
0
 

Author Comment

by:hdcowboyaz
ID: 40259005
This works. How do I put it in the overall query?

SELECT COST, SUM(CostJTD)
FROM (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
LEFT JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7588  
AND o.DELETED = 'N' AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE) AS Z GROUP BY COST
0
 

Author Comment

by:hdcowboyaz
ID: 40275056
Ray,

You asked for the below. I gave you everything and more that you asked for (which took a lot of work) and then you disappeared.

Ray Paseur2014-08-12 at 17:35:20ID: 40257315

Please post the CREATE TABLE statements for these tables.  And please post the test data that can be loaded into these tables and that is needed to illustrate the problem.  Together, these are the SSCCE, an incredibly important concept in software development, and they are the information we need to help you get a solution.  Once we have your starting point and a clear understanding of what you want for the outputs we can almost always help!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40448106
Sorry - I don't know why but I never got any more notifications about the question.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 8 hours left to enroll

873 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