CODEWITHSUNDEEP
sqlsql-servert-sql
Thompho Madula
Thompho Madula

Reputation: 13

Best way to do a Count in TSQL

I am not so good in TSQL and i want to write a report in this manner:

input: Table A

ID   Company Product  Flag
1    A        Car        Y
2    A        Van        N
3    B        Van        Y
4    A        Part       N

Output

Company     Y     N
A           1     2
B           1     0

if one can assist in TSQL...

Upvotes: 0

Views: 81

Answers (4)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

You can use CASE expressions (the people call it "conditional aggregation") to count the flagged products per customer like this (which will ignore a record when the Product column is empty):

SELECT Company
  , COUNT(CASE Flag WHEN 'Y' THEN Product END) AS Y
  , COUNT(CASE Flag WHEN 'N' THEN Product END) AS N
FROM YourTable
GROUP BY Company;

Or you can use this PIVOT query, which is a short form of writing the above:

SELECT Company, Y, N
FROM (SELECT Company, Product, Flag FROM YourTable) AS src
PIVOT (COUNT(Product) FOR Flag IN (Y, N)) AS pvt;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use case when

    select company,
   sum(case when flag='Y' then 1 else 0 end) as Y,
   sum(case when flag='N' then 1 else 0 end) as N from tabe_data
   group by company

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You are looking for conditional aggregation:

select company,
       sum(case when flag = 'Y' then 1 else 0 end) as num_y,
       sum(case when flag = 'N' then 1 else 0 end) as num_n
from t
group by company;

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use conditional aggregation:

SELECT Company
    ,SUM(CASE WHEN Flag = 'Y' THEN 1 ELSE 0 END) AS Y
    ,SUM(CASE WHEN Flag = 'N' THEN 1 ELSE 0 END) AS N
FROM tab
GROUP BY Company

Upvotes: 3

Related Questions