SQL Count Number of Occurrences for a Multi-Row Distinct, By Multi-Row Distinct

Question

I have a query that sums all values from the "Total" column and aligns them to their unique combination of storename, year, make, and model (via a Group By):

    select storename, year, make, model, sum(total) as [Sum]
    from #TempTable
    group by storename, year, make, model

An example of the results:

    StoreA 2009 TOYO    AVALON  1039.95
    StoreB 2005 CHET    TAHOE   1039.99
    StoreC 2010 MAZD    CX-9    1040.07
    StoreD 2007 DODG    CHARGER 1040.09
    StoreE 2003 ACUT    MDX     1040.17

What I want to do is add another column to this query that counts how many rows exist in each Group. For example, I know there are 5 instances of a 2009 TOYO AVALON at StoreA, but I want the script to figure out how many there are for each unique combination of storename, year, make, model. And I want it displayed as an extra column [CarCount] to the right of [Sum].

There must be a way, but I have not been able to find a solution. Thank you for your help!

score 0 · Answer 1 · answered Oct 04 '17 at 23:40

0

Unless I misunderstood, you need the count on the existing grouping. Just use COUNT to get it.

select storename, year, make, model, sum(total) as [Sum], COUNT(1) as CarCount
    from #TempTable
    group by storename, year, make, model

answered Oct 04 '17 at 23:40

AB_87

1,126
8
18

AB_87, thank you for your answer! It is very similar to the one proposed by Valli above. I ran both queries and they produce the same results, so I am wondering what the difference between count(*) and count(1) would be? – Jordan McDonald Oct 05 '17 at 17:47
@JordanMcDonald There was no answer when I submitted mine. We both submitted almost at the same time. It happens. There are many questions around `COUNT(1)` and `COUNT(*)`. Below is the link to one of the most popular questions. https://stackoverflow.com/questions/1221559/count-vs-count1 – AB_87 Oct 05 '17 at 22:15

score 0 · Accepted Answer · answered Oct 04 '17 at 23:40

0

Add count(*) to the query it will count the number of instances in that group

 select storename, year, make, model, sum(total) as [Sum], count(*) carcount 
    from #TempTable
    group by storename, year, make, model

answered Oct 04 '17 at 23:40

Valli

1,440
1
8
13

1

Valli, thank you for your answer. This is exactly what I needed. – Jordan McDonald Oct 05 '17 at 17:46

SQL Count Number of Occurrences for a Multi-Row Distinct, By Multi-Row Distinct

2 Answers2