DiscoDude DiscoDude - 10 months ago 41
SQL Question

how do I use Count to find brands in each month

I have a table where all the columns have data type VARCHAR(MAX).

Is it possible to analyse data where I have date and brands, group it, remove duplicates then use count to find what how many brands found for each month.

To give you simple visual explanation (real data is massive)...

Date Brand
2012-11-12T16:30 SamSung
2012-16-11T12:20 Sony
2012-16-12T09:30 Samsung
2012-16-11T10:00 Sony

Output I would like to see

Date Brand TotalCount
12-2012 Samsung 2
11-2012 Sony 2

I tried to use the following it takes ages then I didn't know to combine the count. I know how to use Count with group(s) but I am not sure if Group By should be used.

SELECT CONVERT(char(8), SomeDate, 4),Brand from SomeTable group by
SomeDate, Brand

Then I tried the following but I don't know how combine Brand and use Count...

SELECT monthYear FROM (
SELECT DISTINCT DATENAME(month,SomeDate) + ' ' +
CONVERT(VARCHAR, DATEPART(year,SomeDate)) monthYear,
CONVERT(varchar(8),SomeDate, 4) orderCol
FROM SomeTable) A

and I am getting an error and I believe it's because there are T's in the SomeDate column...

Conversion failed when converting the varchar value '2012-11-16' to data type int

I am using SQL Server 2014.

Thanks if anyone can help me

Answer Source

As you know storing data in varchar(max) isn't really a good idea. You should use the built-in data types, particularly for numbers and dates.

In any case, the month is just the left 7 characters of the date column, so:

select left(somedate, 7) as yyyymm, brand, count(*)
from someTable
group by left(somedate, 7), brand;