Solved

# SUM the COUNTED records ACROSS a column SQL SERVER 2000

Posted on 2003-02-27
Medium Priority
334 Views
Last Modified: 2008-02-01
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
Question by:jvescio
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
6 Comments

LVL 2

Expert Comment

ID: 8034123
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

LVL 19

Accepted Solution

Rimvis earned 2000 total points
ID: 8034638
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

Expert Comment

ID: 8035093
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

LVL 50

Expert Comment

ID: 8035782
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
where user=::username::
) as Ex

is one solution......
0

Author Comment

ID: 8036249
WELL DONE!!!  This is your code after adding my infoL:

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
FROM exempt WHERE (username =  '::username::')

It worked like a charm!  Thanks again!!
0

LVL 19

Expert Comment

ID: 8039772
Thanx. I'm glad it helped :o)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
###### Suggested Courses
Course of the Month10 days, 16 hours left to enroll

#### 770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.