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.

Answer 1

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

Answer 2

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

Answer 3

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