sql help

I have managed to create a table in SQL SSMS, it has 40000 rows, each representing a customer, i have thirteen columns, the first is the company name and the others are months. Each cell within the grid can have one of three values, either up, down, or static.

What i need to do is aggregate this into three rows, one a count of up one a count of down and one a count of static, and that this be for all 12 month columns.

I hope that makes sense.

hummm months in seperate columns, that might make life difficult

You basically need
SELECT * FROM
(Select count(cust), MonthColJan
FROM Table
GROUP BY monthColJan) a
LEFT JOIN
(Select count(cust), MonthColfeb
FROM Table
GROUP BY monthColfeb) b
ON
MonthColJan = MonthColfeb

and then continue that pattern 12 times, the other solution would be to use unpivot (i think, maybe pivot)
to get your months into rows you can group by

thanks for that, got it to work using an unpivot command to aggregate the fields.

All good now, or it would be if i hadn’t misunderstood the question….

Glad you went for the elegant solution not my Yeeeehaaa hack solution !

was no hack titusrider, amazing to think these days you can just dump 10000000000s or records into excel and manip. as you want, back in the day GROUP BY, HAVING and OUTER JOINS were the mark of a great SQL writer 🙂

I wouldn’t say im great but i do write some most days, i develop Microsoft Data warehouses and reporting in SQL 2008 and 2012

Therefore the excel ‘applications’ you mention are usually what we come in to try and sort out!!

Have you tried Powerpivot? If you are playing with lots of rows in excel and have some DB background it will be perfect for you.

Excel spreadsheets… Hey so convenient that you can stick em on a USB stick and take em home. Now where did I put it again….