CODEWITHSUNDEEP
mysql
BigRedEO
BigRedEO

Reputation: 837

Grouping of AND statements - MySQL

I've been thrown back into MySQL for the first time in 5 years. Writing up queries to run some reports. For one, I needed to find how many gallons of paint by Color ID were dispensed for the year in Division 1. Division is found in a different table than all of our transactions. I wrote this query -

SELECT m.divName, 
n.colorName, 
n.colorID,
SUM(n.gallons) as totalGallons 
FROM mfccnStore m, netTran2014 n 
WHERE (m.division = 1) AND (m.store = n.store) 
GROUP BY colorID
ORDER BY totalGallons DESC 
INTO OUTFILE '/home/Div1All.csv'
FIELDS TERMINATED BY ',';

Ran it, then to test it, I picked one of the most used colors and wrote this query - 

SELECT m.divName, 
n.colorName, 
n.colorID,
n.gallons 
FROM mfccnStore m, netTran2014 n 
WHERE (n.colorID = "6385") AND (m.division = 1) AND (m.store = n.store) 
INTO OUTFILE '/home/DoverWhite.csv'
FIELDS TERMINATED BY ',';

And then verified that the totals for the Color ID "6385" matched after running each query, which they did. Great.

But now it gets a little more complex with a 3rd table needed because they want the same report, but separated by "Interior" and "Exterior" paint. 

SELECT m.divName, 
n.colorName,
n.colorID,
SUM(n.gallons) as totalGallons
FROM mfccnStore m, netTran2014 n, netProduct p 
WHERE ((p.intExt = "INTERIOR") AND (m.division = 1) AND (m.store = n.store)) 
AND ((n.rex = p.prodNbr) AND (n.sizeCode = p.szCd))  
GROUP BY colorID
ORDER BY totalGallons DESC
INTO OUTFILE '/home/Div1Int.csv' 
FIELDS TERMINATED BY ',';

This gives me totals that are a little bit more than double the other two queries and I'm sure it must have to do with the order of my WHERE clause - and where the parentheses are being placed. Any ideas/help would be greatly appreciated.

Upvotes: 0

Views: 40

Answers (2)

sgeddes
sgeddes

Reputation: 62831

First, I'd suggest using the JOIN syntax. Then it reads like this:

SELECT m.divName,
       n.colorName,
       n.colorID,
       p.intExt
       SUM(n.gallons) totalGallons
FROM mfccnStore m
    JOIN netTran2014 n ON m.store = n.store
    JOIN netProduct p ON n.rex = p.prodNbr AND n.sizeCode = p.szCd
WHERE p.intExt IN ('INTERIOR','EXTERIOR') 
    AND m.division = 1
GROUP BY n.colorID, p.intExt
ORDER BY totalGallons DESC

Be careful which fields you put in the GROUP BY -- most RDBMS require all fields to be included that aren't included in aggregate functions. MySQL is a little more flexible, but can return inaccurate results.

Upvotes: 1

ajacian81
ajacian81

Reputation: 7569

You don't need to overcomplicate it. The break down can be handled with an additional GROUP BY clause, in this case GROUP BY p.intExt;

Upvotes: 0

Related Questions