Counting multiple columns in one table/record

Question

Counting multiple columns in one table/record

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
March 22, 2010 at 12:04 pm

#231104

Greetings to the group...
I'm not sure this can be done (I've done several google searches and can't find exactly what I am doing.)
I have the following statement with two unions in it to count three different fields all from one table...
SELECT
SITEID,
DATEPART(YYYY, REQUESTDATE) AS YEAR,
COUNT(1) AS 'NO OF WORK ORDERS OPENED',
0 AS 'NO OF WORK ORDERS CLOSED',
0 AS 'NO OF WORK ORDERS STILL OPEN'
FROM WO
GROUP BY SITEID, DATEPART(YYYY, REQUESTDATE)
UNION
SELECT
SITEID,
DATEPART(YYYY, REQUESTDATE) AS YEAR,
0 AS 'NO OF WORK ORDERS OPENED',
COUNT(1) AS 'NO OF WORK ORDERS CLOSED',
0 AS 'NO OF WORK ORDERS STILL OPEN'
FROM WO
WHERE CLOSEDATE <> '01/01/1900'
GROUP BY SITEID, DATEPART(YYYY, REQUESTDATE)
UNION
SELECT
SITEID,
DATEPART(YYYY, REQUESTDATE) AS YEAR,
0 AS 'NO OF WORK ORDERS OPENED',
0 AS 'NO OF WORK ORDERS CLOSED',
COUNT(1) AS 'NO OF WORK ORDERS STILL OPEN'
FROM WO
WHERE CLOSEDATE = '01/01/1900'
GROUP BY SITEID, DATEPART(YYYY, REQUESTDATE)
ORDER BY SITEID, DATEPART(YYYY, REQUESTDATE)
This is my result of one SITEID for one year... (I couldn't get these results to format correctly so I simply inserted a "/" between each column.
SITEID / YEAR / OPENED / CLOSED / STILL OPEN
-----------------------------------------------------------
HEND-CORP / 2010 / 0 / 0 / 251
HEND-CORP / 2010 / 0 / 410 / 0
HEND-CORP / 2010 / 661 / 0 / 0
What I would like to see in my result set is one line per SITEID per year... for example...
HEND-CORP / 2010 / 661 / 410 / 251
Is this possible?
Many thanks in advance!
Bob
This is a SQL 2000 database...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

toolman-352714 SSCrazy Points: 2495 More actions · Answer 1

Select your data into a temp table and do a final select that sums the counted fields and groups by the location and year.

That's one quick and dirty way to do it.

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think you want something like this, but the key is which column has the "status", and it's data type:

i assummed a string field, but you'll get the idea once you identify the column;

basically by using the SUM() of a CASE(), you can get all your counts in a single pass.

SELECT

SITEID,

DATEPART(YYYY, REQUESTDATE) AS YEAR,

SUM (CASE WHEN STATUS = 'OPEN' THEN 1 ELSE 0 END END ) AS 'NO OF WORK ORDERS OPENED',

SUM (CASE WHEN STATUS = 'CLOSED' THEN 1 ELSE 0 END END ) AS 'NO OF WORK ORDERS CLOSED',

SUM (CASE WHEN STATUS = 'PENDING' THEN 1 ELSE 0 END END ) AS 'NO OF WORK ORDERS STILL OPEN'

FROM WO

GROUP BY SITEID, DATEPART(YYYY, REQUESTDATE)

-----------edit-----------------

doh! i just saw it's based on the date CLOSEDATE = '01/01/1900'

SELECT

SITEID,

DATEPART(YYYY, REQUESTDATE) AS YEAR,

COUNT(1) AS 'NO OF WORK ORDERS OPENED',

SUM (CASE WHEN CLOSEDATE = '01/01/1900' THEN 1 ELSE 0 END END ) AS 'NO OF WORK ORDERS CLOSED',

SUM (CASE WHEN CLOSEDATE != '01/01/1900' THEN 1 ELSE 0 END END ) AS 'NO OF WORK ORDERS STILL OPEN'

FROM WO

GROUP BY SITEID, DATEPART(YYYY, REQUESTDATE)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 3

Bob Bridges-173967

SSCrazy

Points: 2094

March 22, 2010 at 12:33 pm

#1137771

Lowell,

This works perfectly... thanks very much!

toolman-352714 SSCrazy Points: 2495 More actions · Answer 4

I'd go with Lowell's solution instead of my temp table option.

[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 5

toolman-352714 (3/22/2010)
I'd go with Lowell's solution instead of my temp table option.

Thanks toolman... and I like your signature passage!