CODEWITHSUNDEEP
sql-servercount
user3486647
user3486647

Reputation: 311

How to count # of specific columns in sql?

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?

Upvotes: 0

Views: 119

Answers (3)

Monis Qureshi
Monis Qureshi

Reputation: 17

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

Upvotes: -1

stuartd
stuartd

Reputation: 73243

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 ...

Upvotes: 2

imgnx
imgnx

Reputation: 789

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';

Upvotes: 0

Related Questions