SQL Column / Row Grouping

Question

I am new to SQL. I am looking for a simple SQL solution to combine a row/column for row whose column contain the same data, in this case a zip code. For example, the data look looks like this:

state, county, city, zip, count

"CA","ALAMEDA","HAYWARD","94541",5371
"CA","ALAMEDA","HAYWARD","94542",2209
"CA","ALAMEDA","HAYWARD","94544",7179
"CA","ALAMEDA","HAYWARD","94545",4209
"CA","ALAMEDA","CASTRO VALLEY","94546",7213
"CA","ALAMEDA","HAYWARD","94546",37
"CA","ALAMEDA","LIVERMORE","94550",9809
"CA","ALAMEDA","LIVERMORE","94551",6558
"CA","ALAMEDA","CASTRO VALLEY","94552",3121
"CA","ALAMEDA","HAYWARD","94552",12
"CA","ALAMEDA","FREMONT","94555",5392

I'd like to end up with the data to look like this:

state, county, city, zip, count

"CA","ALAMEDA","HAYWARD","94541",5371
"CA","ALAMEDA","HAYWARD","94542",2209
"CA","ALAMEDA","HAYWARD","94544",7179
"CA","ALAMEDA","HAYWARD","94545",4209
"CA","ALAMEDA","CASTRO VALLEY / HAYWARD","94546",7250
"CA","ALAMEDA","LIVERMORE","94550",9809
"CA","ALAMEDA","LIVERMORE","94551",6558
"CA","ALAMEDA","CASTRO VALLEY HAYWARD","94552",3133
"CA","ALAMEDA","FREMONT","94555",5392

You can see that in two rows the data has been combined or summed. For rows that contain the exact same zip code, the city names (both) appear in the city column and the count is the sum of the count from each row.

Is there any way to do this using SQL? Even if it requires two different SQL statements that is fine.

Answer 1

Assuming SQL Server, you can use FOR XML to get your desired results.

select distinct t.state,t.county,t.zip,t2.sumcount,
STUFF(
        (
            SELECT '/' + city AS [text()]
            FROM mytable t3
            WHERE t.zip = t3.zip
            FOR XML PATH('')
        ), 1, 1, '') AS ColList
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip

And some SQL Fiddle.

If you are using MySQL, look at using GROUP_CONCAT:

select distinct t.state,t.county,t.zip,t2.sumcount,
GROUP_CONCAT(t.city) as cities
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip
GROUP BY t.state,t.county,t.zip,t2.sumcount

And more Fiddle.

Good luck.