CODEWITHSUNDEEP
sqlsql-servert-sql
Hema
Hema

Reputation: 135

SQL count for different columns based on a particular column

I have a table like this:

   CREATE TABLE [dbo].[Actual_FTE](
    [EName] [nvarchar](50) NULL,
    [ProgramName] [nvarchar](50) NULL,
    [January] [nvarchar](50) NULL,
    [February] [nvarchar](50) NULL,
    [March] [nvarchar](50) NULL,
    [April] [nvarchar](50) NULL,
    [May] [nvarchar](50) NULL,
    [June] [nvarchar](50) NULL,
    [July] [nvarchar](50) NULL,
    [August] [nvarchar](50) NULL,
    [September] [nvarchar](50) NULL,
    [October] [nvarchar](50) NULL,
    [November] [nvarchar](50) NULL,
    [December] [nvarchar](50) NULL,
    [Year] [nvarchar](6) NULL,
    [Scenario] [nvarchar](10) NULL
  ) ON [PRIMARY]

The sample data is 

      Insert into Actual_FTE values
     ('Anu ','Pgm1',  '0.2',  '0',  '0.5', ' 0.4', ' 0.6',  '0.7','0.8',  '0.4','0.3', ' 0.4','0.3',  '0.2','2017',  'Actual'),                             
     ('Barry ','Pgm1',  '0.4',  '0.6',  '0.3', ' 0.1', ' 0',  '0.8','0.8',  '0.7','0.8', ' 0.9','0.3',  '0.3','2017',  'Actual'),                               

     ('Anu ','Pgm1',  '1',  '1',  '0.8', ' 0.8', ' 0.9',  '0.9','0.9',  '1','1', ' 1','0.7',  '0.7','2017',  'Forecast 1'),                             
    ('Barry ','Pgm1',  '0.8',  '0.7',  '0.7', ' 0.9', ' 0.7',  '0.4','0.5',  '0.6','0.8', ' 0.8','0.8',  '0.8','2017',  'Forecast 1'),                              

     ('Betsy ','Pgm1',  '0.8',  '0.6',  '0.4', ' 0.3', ' 0.3',  '0.3','0.2',  '0.1','0.3', ' 0.4','0.6',  '0.5','2017',  'Forecast 1'),                             

     ('Cory ','Pgm1',  '0.8',  '0.6',  '0.7', ' 0.4', ' 0.3',  '0.4','0.3',  '0.5','0.2', ' 0.3','0.2',  '0.1','2017',  'Forecast 1'),                              

   ('Anu ','Pgm1',  '0.5',  '0.3',  '0.4', ' 0.5', ' 0.4',  '0.4','0.8',  '0.2','0.8', ' 0.6','0.8',  '0.7','2017',  'Forecast 2'),                             

     ('Betsy ','Pgm1',  '0.8',  '0.6',  '0.7', ' 0.8', ' 0.9',  '0.4','0.3',  '0.5','0.4', ' 0.5','0.4',  '0.2','2017',  'Forecast 2'),                             

     ('Cory ','Pgm1',  '0.3',  '0.5',  '0.6', ' 0.5', ' 0.5',  '0.6','0.8',  '0.9','1', ' 1','1',  '0.9','2017',  'Forecast 2'),                                
     ('Barry ','Pgm1',  '0.2',  '0',  '0.5', ' 0.4', ' 0.6',  '0.7','0.8',  '0.4','0.3', ' 0.4','0.3',  '0.2','2017',  'Forecast 3'),                               

     ('Betsy ','Pgm1',  '0.4',  '0.6',  '0.3', ' 0.1', ' 0',  '0.8','0.8',  '0.7','0.8', ' 0.9','0.3',  '0.3','2017',  'Forecast 3')

The idea is to find out how many employees are working on a Program throughout the calender year for various scenarios - Actual and some forecast data. I want to get the count of employees for each of the scenarios.If the value is 0 for any mnonth, it indicates that an employee is working on that month and should not be counted. For example the output should be like this:

 Program Jan  Feb Mar Apri May Jun July Aug Sep Oct Nov Dec Year Scenario
 Pgm1     2    1    2   2   1   2   2    2   2   2   2   2   2017  Actual
 Pgm1     4    4    4   4   4   4   4    4   4   4   4   4   2017  Forecast1
 Pgm1     3    3    3   3   3   3   3    3   3   3   3   3   2017  Forecast2
 Pgm1     2    1    2   2   2   1   2    2   2   2   2   2   2017  Forecast3

Please let me know how to do it.

Thanks, Hema

Upvotes: 1

Views: 52

Answers (2)

Marek Vitek
Marek Vitek

Reputation: 1633

Slightly improved version of @Greenspak to cover cases where numbers are incorrectly entered with leading spaces. As it is in your sample data. See for May.

SELECT
    ProgramName,
    COUNT (DISTINCT Case convert(float,January) When 0 then Null else EName end) as January,
    COUNT (DISTINCT Case convert(float,February) When 0 then Null else EName end) as February,
    COUNT (DISTINCT Case convert(float,March) When 0 then Null else EName end) as March,
    COUNT (DISTINCT Case convert(float,April) When 0 then Null else EName end) as April,
    COUNT (DISTINCT Case convert(float,May) When 0 then Null else EName end) as May,
    COUNT (DISTINCT Case convert(float,June) When 0 then Null else EName end) as June,
    COUNT (DISTINCT Case convert(float,July) When 0 then Null else EName end) as July,
    COUNT (DISTINCT Case convert(float,August) When 0 then Null else EName end) as August,
    COUNT (DISTINCT Case convert(float,September) When 0 then Null else EName end) as September,
    COUNT (DISTINCT Case convert(float,October) When 0 then Null else EName end) as October,
    COUNT (DISTINCT Case convert(float,November) When 0 then Null else EName end) as November,
    COUNT (DISTINCT Case convert(float,December) When 0 then Null else EName end) as December,
    [Year],
    Scenario
From Actual_FTE
Group by
    ProgramName,
    [Year],
    Scenario

Upvotes: 1

Ryan B.
Ryan B.

Reputation: 3665

You can take advantage of the way count ignores a null to get a distinct count of enames that ignores the rows where a field's value is '0'.

SELECT
    ProgramName
    , COUNT (DISTINCT Case January When '0' then Null else EName end) as January
    , COUNT (DISTINCT Case February When '0' then Null else EName end) as February
    , COUNT (DISTINCT Case March When '0' then Null else EName end) as March
    , COUNT (DISTINCT Case April When '0' then Null else EName end) as April
    , COUNT (DISTINCT Case May When '0' then Null else EName end) as May
    , COUNT (DISTINCT Case June When '0' then Null else EName end) as June
    , COUNT (DISTINCT Case July When '0' then Null else EName end) as July
    , COUNT (DISTINCT Case August When '0' then Null else EName end) as August
    , COUNT (DISTINCT Case September When '0' then Null else EName end) as September
    , COUNT (DISTINCT Case October When '0' then Null else EName end) as October
    , COUNT (DISTINCT Case November When '0' then Null else EName end) as November
    , COUNT (DISTINCT Case December When '0' then Null else EName end) as December
    , [Year]
    , Scenario
From Actual_FTE
Group by
    ProgramName
    , [Year]
    , Scenario

gets you this:

ProgramName    January     February    March       April       May         June        July        August      September   October     November    December    Year   Scenario
-------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------ ----------
Pgm1           2           1           2           2           2           2           2           2           2           2           2           2           2017   Actual
Pgm1           4           4           4           4           4           4           4           4           4           4           4           4           2017   Forecast 1
Pgm1           3           3           3           3           3           3           3           3           3           3           3           3           2017   Forecast 2
Pgm1           2           1           2           2           2           2           2           2           2           2           2           2           2017   Forecast 3

Upvotes: 2

Related Questions