Topic: Group by 2 or more fields

[travelkind]

I was wondering if there was a way to group by 2 or more fields?  Below is my current group by for one field:

$query = "SELECT Account_and_Name, SUM(Ship_Quantity) FROM rawdata GROUP BY Account_and_Name";

I need to also group by inv_date (invoice date) which would be a range of say 04/01/2010 through 04/30/2010 and 05/01/2010 through 05/31/2010.  How is this done?  Thanks for your help.

[DavidAM]

Just include the column in the SELECT and the GROUP BY
$query = "SELECT Account_and_Name, inv_date, SUM(Ship_Quantity) 
FROM rawdata GROUP BY Account_and_Name, inv_date";

[travelkind]

Okay so they are just seperated by commas.  Pardon my ignorance I am trying to learn php and mysql currently.  One other question, I want my Ship_Quantity to be an aggregate function of SUM but the Invoice_Date needs show a range of say 04/01/2010 through 04/30/2010.  I guess this could be done through and aggregate of MIN and MAX?  So going back to your modification of my code how do I assign these?  Thanks again for your help.

$query = "SELECT Account_and_Name, inv_date, SUM(Ship_Quantity) FROM rawdata GROUP BY Account_and_Name, inv_date";

[DavidAM]

No apologies necessary. Helping people is why we are here. Believe it or not, everyone on this board was ignorant about mySql, PHP, and whatever, before they started doing it.

I'm not sure I understand the question.

Do you want the total quantity shipped on each day within that range? like this:

Code: [Select]

Customer1   04/01/2010  12
Customer1   04/02/2010  5
Customer2   04/02/2010  7
This is the results you will get with the changes we made to the query before.

Or are you looking for the total that was shipped for each customer in that date range? like this

Code: [Select]

Customer1   17
Customer2   7
This is the results you will get from your original query

The query will be different depending on what you need.

To limit your results to the date range, you add a WHERE clause to the query.  If your inv_date is defined as a DATE or DATETIME column, the clause would be:

Code: [Select]

WHERE inv_date BETWEEN '2010-04-01' AND '2010-04-30'

[travelkind]

What I am trying to do is group my data by customer and then have the corresponding gallons for that month show up on a line.

For example, if customer1 had 2 sales in April one on 04/12/10 with 10 gallons and another on 04/18/10 with 15 gallons.  Now in May we have sales on 05/16/10 with 20 gallons and on 05/20/10 27 gallons.  Here is how it should display:

Customer1  April 2010  25 (gallons)
Customer1  May 2010  47 (gallons)

[DavidAM]

Ahh, then you want to group by a portion of the date field.  You will definitely need to make sure your inv_date is a DATE or DATETIME field, this would be REALLY complicated if it were a VARCHAR.

Then in the SELECT statement you want the customer name and month and year, so it would be something along these lines (plug in your fields as appropriate):

Code: [Select]

SELECT Customer, YEAR(InvoiceDate) AS IYear, MONTH(InvoiceDate) AS IMonth, SUM(Qty) AS Total
FROM TableName
WHERE InvoiceDate BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Customer, YEAR(InvoiceDate), MONTH(InvoiceDate)I usually put the year first so that the sort works out correctly if you span years.

If you want to pull back the Month Name, add the MONTH_NAME(InvoiceDate) function to the SELECT. You will want to leave the MONTH() function in THE GROUP_BY so it sorts numerically instead of alphabetically.

[travelkind]

I will want to pull the month name back I may use this for a header for the column.  Here is what my code looks like with your recomendations:

$query = "SELECT Account_and_Name, YEAR(Invoice_Date) AS IYear, MONTH(Invoice_Date) AS IMonth, SUM(Ship_Quantity) AS Total FROM rawdata
WHERE Invoice_Date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Account_and_Name, YEAR(Invoice_Date), MONTH(Invoice_Date)";

Where exactly do I need to add the MONTH_NAME(Invoice_Date)?  I guess to get my desired output I need to echo out the proper fields.

[DavidAM]

Just add it to the SELECT list (instead of Invoice_Date, isn't your column named inv_date?)

Code: [Select]

$query = "SELECT Account_and_Name, YEAR(inv_date) AS IYear, MONTH(inv_date) AS IMonth,
SUM(Ship_Quantity) AS Total, MONTH_NAME(inv_date) AS MName
FROM rawdata
WHERE inv_date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Account_and_Name, YEAR(Invoice_Date), MONTH(Invoice_Date)";

[travelkind]

It is actually Invoice_Date not inv_date.  I got the following error when trying to run it:

FUNCTION "MY Database Name".MONTH_NAME does not exist

Here is my code:

$query = "SELECT Account_and_Name, YEAR(Invoice_Date) AS IYear, MONTH(Invoice_Date) AS IMonth, SUM(Ship_Quantity) AS Total, MONTH_NAME(Invoice_Date) AS MName
FROM rawdata
WHERE Invoice_Date BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY Account_and_Name, YEAR(Invoice_Date), MONTH(Invoice_Date)";

[DavidAM]

Oops, sorry.  There is no underscore in the function name: MONTHNAME(Invoice_Date)

[travelkind]

Okay I see that and should have noticed it myself!  Okay the error is gone!  I am going to work on echoing my data fields out now.  Thanks a lot for your help!