How to count # of specific columns in sql?

Question

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40

week2: 24

Week3: 24

Week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

stuartd · Accepted Answer · 2015-07-11 22:40:20Z

2

Something like this should do it:

SELECT EmployeeID, 
    (CASE WHEN Week1 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week2 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week3 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week4 < 32 THEN 1 ELSE 0 END) AS Count
  FROM Employees ...

edited Jul 11, 2015 at 22:40

answered Jul 11, 2015 at 22:30

stuartd

73.7k16 gold badges139 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

Looks like plain addition should work without any aggregates at all.

imgnx · Accepted Answer · 2015-07-11 22:35:32Z

0

SELECT COUNT(*)
FROM [TableName];

You can use the

WHERE statement after SELECT ... FROM ... to specify conditions such as Employee's with hours below 32.

For example:

SELECT COUNT(*)
FROM [TableName]
WHERE `Hours`<'32';

Use the AND operator to add more conditions

SELECT COUNT(*)
FROM [TableName]
WHERE `Hours`<'32' AND `EmployeeName`='Todd';

edited Jul 11, 2015 at 22:35

answered Jul 11, 2015 at 22:28

imgnx

7962 gold badges13 silver badges31 bronze badges

Comments

Pengyy · Accepted Answer · 2017-03-08 09:29:23Z

SELECT 'MEMBERS(ACTIVE ACCOUNTS)' particular, ''value
union all
SELECT +'    '+ member_type,  
    (SELECT active_students = (SELECT COUNT(member_type_id) FROM LM_MEMBER_MASTER 
     WHERE institute_code = 'GDAB' AND member_type_id = A.member_type_id AND status = 'Active'))
FROM LM_MEMBER_TYPE_MASTER A WHERE institute_code = 'GDAB' 
union all
Select '    Total' particular,  COUNT(member_type_id) FROM LM_MEMBER_MASTER 
WHERE institute_code = 'GDAB'  AND status = 'Active'
union all
SELECT 'MEMBERS(CLOSED ACCOUNTS)' particular, '' value
union all
SELECT +'    '+member_type,  
    (SELECT active_students = (SELECT COUNT(member_type_id) FROM LM_MEMBER_MASTER 
     WHERE institute_code = 'GDAB' AND member_type_id = A.member_type_id AND status = 'Closed'))
FROM LM_MEMBER_TYPE_MASTER A WHERE institute_code = 'GDAB' 
union all
Select '    Total' particular,  COUNT(member_type_id) FROM LM_MEMBER_MASTER 
WHERE institute_code = 'GDAB'  AND status = 'Closed'

in this code i dont want to count 'MEMBERS(ACTIVE ACCOUNTS)' and 'MEMBERS(CLOSED ACCOUNTS)' bcoz its headlines

Collectives™ on Stack Overflow

How to count # of specific columns in sql?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related