CODEWITHSUNDEEP
sqlsql-serveroracle-database
Cognos Bee
Cognos Bee

Reputation: 29

Sum of Char value in one column

I need to count the different values in one column. It is Char data type. I wrote my code in oracle which works but I cannot change the syntax in SQL server;

This works:

Select l.name, l.address,
       SUM(DECODE(L.SEX,   'M',   1,   0)) MALE_TOTAL,
       SUM(DECODE(L.SEX,   'F',   1,   0)) FEMALE_TOTAL,
       SUM(DECODE(L.SEX,   'U',   1,   0)) UNKNOWN_TOTAL
From personnel

Thanks a bunch

Upvotes: 0

Views: 4284

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

you can use IIF 2012 +

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql

SELECT
  SUM(IIF(L.SEX = 'M', 1, 0)) MALE_TOTAL,
  SUM(IIF(L.SEX = 'F', 1, 0)) FEMALE_TOTAL,
  SUM(IIF(L.SEX = 'U', 1, 0)) UNKNOWN_TOTAL
FROM personnel

OR CASE

SELECT
  SUM(case when L.SEX = 'M' then 1 else 0 end)) MALE_TOTAL,
  SUM(case when L.SEX = 'F' then 1 else 0 end) FEMALE_TOTAL,
  SUM(case when L.SEX = 'U' then 1 else 0 end) UNKNOWN_TOTAL
FROM personnel

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269733

The SQL standard method for what you want is:

Select l.name, l.address,
       SUM(CASE WHEN L.SEX = 'M' THEN 1 ELSE 0 END) MALE_TOTAL,
       SUM(CASE WHEN L.SEX = 'F' THEN 1 ELSE 0 END) FEMALE_TOTAL,
       SUM(CASE WHEN L.SEX = 'U' THEN 1 ELSE 0 END) UNKNOWN_TOTAL
From personnel
group by l.name, l.address;

Note the GROUP BY. If you want one row per name/address, then you need this. Otherwise remove the columns from the SELECT:

Select SUM(CASE WHEN L.SEX = 'M' THEN 1 ELSE 0 END) MALE_TOTAL,
       SUM(CASE WHEN L.SEX = 'F' THEN 1 ELSE 0 END) FEMALE_TOTAL,
       SUM(CASE WHEN L.SEX = 'U' THEN 1 ELSE 0 END) UNKNOWN_TOTAL
From personnel;

Upvotes: 1

Related Questions