• Status: Solved
• Priority: Medium
• Security: Public
• Views: 340

# SUM the COUNTED records ACROSS a column SQL SERVER 2000

THIS IS A TIME SLIP SCENARIO...using SQL SERVER 2000

I have a table (exempt) with the days of the week represented as columns (MONam, MONpm, TUESam, TUESpm, WEDam, WEDpm and so on).

For each day (am and pm) I am allowing a FORM to pass a value of  P or S or H (p=present, S=Sick, H=holiday)

Edning up with...
MONam: P MONpm: P
TUEam: P TUEpm: P
WEDam: H WEDpm: H
THURam: P THURpm: P
FRIam: S FRIpm: S

I would like to COUNT the number of Ps, Ss and Hs across the column (for each record) and then SUM that COUNT.

Ending up with a summary for the week/username

P= 6*.5=3
H= 2*.5=1
S= 2*.5=1
TOTAL= 5

This is what I have so far:

SELECT COUNT(MONam,MONpm,TUESam,TUESpm,WEDam,WEDpm, THURam,THURpm,FRIam,FRIpm) AS hours, SUM(hours * .5) AS days FROM exempt WHERE (username =  '::username::')

Any assistance is MUCH appreciated!!
0
jvescio
1 Solution

Commented:
My suggestion is tedious, and someone may have a slicker suggestion, but:
1) Create feeder queries for each day and value:
SELECT COUNT(*) AS hours, FROM exempt WHERE (username =  '::username::') and (MONam = 'P')
2) Create 3 union queries, one for each value:
select hours from qry_MONam_P
UNION
select hours from qry_MONpm_P
etc.
3) For each union query, create another query to do the sum
Use AS PHours, HHours, SHours
4) Your final query gets the sums from the 3 sum queries

A pain, but it will work.  Hope someone has a better idea.
0

Commented:
Try query like this:
(replace field names, add '* 0.5', etc.)

SELECT
SUM(CASE X1 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'P' THEN 1 ELSE 0 END)AS Ps,
SUM(CASE X1 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'S' THEN 1 ELSE 0 END) AS Ss,
SUM(CASE X1 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'H' THEN 1 ELSE 0 END) AS Hs,
SUM(CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END) * 0.5 AS TOTAL
FROM days
0

Commented:
It's probably too late now, but for the future I would suggest you normalise your tables properly. Normalisation theory may seem like mumbo-jumbo, but there are very good, practical, reasons for it, and you have found one of them.

pp Codd & Date :)
0

Commented:
Select convert(float,sum(p)) * 0.5 as P,
convert(float,sum(s)) * 0.5 as S,
convert(float,sum(h)) * 0.5 as H,
convert(float,Sum(p+s+h)) * 0.5 as Total
From (Select
Case when MonAm='P' Then 1 else 0 end
+ Case when Monpm='P' Then 1 else 0 end
...
+ Case when Sunpm='P' Then 1 else 0 end
as P
, Case when MonAm='S' Then 1 else 0 end
+ Case when Monpm='S' Then 1 else 0 end
...
+ Case when Sunpm='S' Then 1 else 0 end
as S
, Case when MonAm='H' Then 1 else 0 end
+ Case when Monpm='H' Then 1 else 0 end
...
+ Case when Sunpm='H' Then 1 else 0 end
as H
From Exempt
) as Ex

is one solution......
0

Author Commented:

SELECT
SUM(CASE MONam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'WO' THEN 1 ELSE 0 END)  * 0.5 AS WOs,
SUM(CASE MONam WHEN 'J' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'J' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'J' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'J' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'J' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'J' THEN 1 ELSE 0 END)  * 0.5 AS Js,
SUM(CASE MONam WHEN 'B' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'B' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'B' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'B' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'B' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'B' THEN 1 ELSE 0 END)  * 0.5 AS Bs,
SUM(CASE MONam WHEN 'V' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'V' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'V' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'V' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'V' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'V' THEN 1 ELSE 0 END)  * 0.5 AS Vs,
SUM(CASE MONam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'PE' THEN 1 ELSE 0 END)  * 0.5 AS PEs,
SUM(CASE MONam WHEN 'P' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'P' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'P' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'P' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'P' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'P' THEN 1 ELSE 0 END)  * 0.5 AS Ps,
SUM(CASE MONam WHEN 'S' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'S' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'S' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'S' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'S' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'S' THEN 1 ELSE 0 END)  * 0.5 AS Ss,
SUM(CASE MONam WHEN 'H' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'H' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'H' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'H' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'H' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'H' THEN 1 ELSE 0 END)  * 0.5 AS Hs,
SUM(CASE WHEN MONam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN MONpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN TUEam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN TUEpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN WEDam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN WEDpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN THURam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN THURpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN FRIam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN FRIpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END) * 0.5 AS TOTAL