[Resolved] mySQL GROUP BY

sstonge

Hey all,

I'm back in the mySQL game, this time I was hoping to extract some data without using php...

I have a database from a POS system full of customer data. The important Columns are Postal_Code and Amount_Spent.

I know I can use the GROUP BY command to group the customs by Postal_Code, but I only want to group them by the first 3 characters of the Postal_Code field.

Then for each group I need to show the Amount_Spent total for that group...

I know how to do this in php, but I wanted to avoid installing anything extra on the POS computer.

Can I do this in a few lines in mySQL using something like the MySQL Query Browser (http://dev.mysql.com/downloads/query-browser/index.html)?

Thanks in advance
😃

sstonge

I was looking in some other posts and I think something like this would work... but still not grouping by only the 1st 3 chars in the Postal_Code...

(I haven't tested this yes as I am not at the store...)

SELECT Postal_Code, SUM(Amount_Spent) AS Amount_Spent FROM table_name GROUP BY Postal_Code

Drakla

GROUP BY LEFT(Postal_Code, 3);

sstonge

Thanks Drakla!

I'll try that...

What do you think would work better out of these 2 lines?

SELECT Postal_Code, SUM(Amount_Spent) FROM table_name GROUP BY LEFT(Postal_Code, 3)

SELECT Postal_Code, SUM(Amount_Spent) AS Amount_Spent FROM table_name GROUP BY LEFT(Postal_Code, 3)

Drakla

Go for the 2nd as it's easier to pull from your results from the array using its associative alias name. If you're just using a numeric array index then it doesn't really matter.