question on Group by clause in Sql server

Question

Table: Customer

Name    Type    Amount

James   P   125.00
James   P   125.00
James   P   125.00
James   R   225.00
James   R   225.00
Rajiv   R   155.00
Rajiv   R   155.00
Rajiv   R   155.00
Rajiv   P   150.00
Rajiv   P   150.00
Saran   R   175.00

In this table structure I want a output which will give each person’s count of P, count of R, sum of Amount where type = P, Sum of amount where type = R

Any clues for me as stuck up with group by did not help me much in this scenario.

score 3 · Answer 1 · answered Aug 19 '10 at 06:07

If you want the result as separate records, you simply group on the name and type:

select Name, Type, count(*) as Cnt, sum(Amount) as AmountSum
from Customer
Group by Name, Type
order by Name, Type

Result:

Name    Type  Cnt  AmountSum
James   P     3    375.00
James   R     2    450.00
Rajiv   P     2    300.00
Rajiv   R     3    465.00
Saran   R     1    175.00

If you want the count and sum for a person in the same record, you have to do some comparisons:

select
  Name,
  count(case Type when 'P' then 1 else null end) as CntP,
  sum(case Type when 'P' then Amount else 0 end) as AmountSumP,
  count(case Type when 'R' then 1 else null end) as CntR,
  sum(case Type when 'R' then Amount else 0 end) as AmountSumR,
from Customer
Group by Name
order by Name

Result:

Name    CntP  AmountSumP  CntR  AmountSumR
James   3     375.00      2     450.00
Rajiv   2     300.00      3     465.00
Saran   0     0.00        1     175.00

+1 for showing both. There is a third way ... use first approach then use ROLLUP. — TomTom, Aug 19 '10 at 06:17

gbn · Answer 2 · 2010-08-19T06:38:17.817

One query, no CTE, no derived tables:

SELECT
    Name,
    SUM(CASE WHEN Type = 'P' THEN 1 ELSE 0 END) AS PCount,
    SUM(CASE WHEN Type = 'R' THEN 1 ELSE 0 END) AS RCount,
    SUM(CASE WHEN Type = 'P' THEN Amount ELSE 0 END) AS PAmount,
    SUM(CASE WHEN Type = 'R' THEN Amount ELSE 0 END) AS RAmount
FROM yourTable 
GROUP BY Name

using the

CREATE TABLE customer (name varchar(50), type char(1), amount decimal(6,2));

INSERT INTO customer VALUES ('James', 'P', 125.00);
INSERT INTO customer VALUES ('James', 'P', 125.00);
INSERT INTO customer VALUES ('James', 'P', 125.00);
INSERT INTO customer VALUES ('James', 'R', 225.00);
INSERT INTO customer VALUES ('James', 'R', 225.00);
INSERT INTO customer VALUES ('Rajiv', 'R', 155.00);
INSERT INTO customer VALUES ('Rajiv', 'R', 155.00);
INSERT INTO customer VALUES ('Rajiv', 'R', 155.00);
INSERT INTO customer VALUES ('Rajiv', 'P', 150.00);
INSERT INTO customer VALUES ('Rajiv', 'P', 150.00);
INSERT INTO customer VALUES ('Saran', 'R', 175.00);
SELECT
    Name,
    SUM(CASE WHEN Type = 'P' THEN 1 ELSE 0 END) AS PCount,
    SUM(CASE WHEN Type = 'R' THEN 1 ELSE 0 END) AS RCount,
    SUM(CASE WHEN Type = 'P' THEN Amount ELSE 0 END) AS PAmount,
    SUM(CASE WHEN Type = 'R' THEN Amount ELSE 0 END) AS RAmount
FROM customer 
GROUP BY Name

James   3   2   375.00  450.00
Rajiv   2   3   300.00  465.00
Saran   0   1   0.00    175.00

@Muhammad Kashif Nadeem: I have the same values as guffa... see my comment why your answer is wrong — gbn, Aug 19 '10 at 06:39

Kashif · Answer 3 · 2010-08-19T07:33:58.367

0

Edited Answer: After gbn pointed out a mistake in my original answer

SELECT  name,
        SUM( CASE WHEN [type] = 'P' THEN 1 ELSE 0 END) CountOfP , 
        SUM( CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) CountOfR,
        SUM( CASE WHEN [type] = 'P' THEN Amount End) SumOfP , 
        SUM( CASE WHEN [type] = 'R' THEN Amount END) SumOfR

FROM customer 
GROUP BY name

Original Answer

SELECT  name,
        COUNT( CASE WHEN [type] = 'P' THEN 1 ELSE 0 END) CountOfP , 
        COUNT( CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) CountOfR,
        SUM( CASE WHEN [type] = 'P' THEN Amount End) SumOfP , 
        SUM( CASE WHEN [type] = 'R' THEN Amount END) SumOfR

FROM customer 
GROUP BY name

edited Aug 19 '10 at 07:33

answered Aug 19 '10 at 06:13

Kashif

14,071
18
66
98

I am a little late but glad that my answer is somewhat like Gurus like gbn and Guffa. – Kashif Aug 19 '10 at 06:14
it's wrong though. COUNT(0) is the same as COUNT(1) becuase you're counting non-null expressions so you'll always count all types. See my answer here http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 – gbn Aug 19 '10 at 06:40

question on Group by clause in Sql server

3 Answers3