Query with Mathematic function to sum number of fields with a certain status

Question

Query with Mathematic function to sum number of fields with a certain status

Magnus T

Valued Member

Points: 54
More actions
May 30, 2007 at 8:32 am

#178854

Hey guys,
I have a bit of a problem with solving this maybe you can help?
we have recieved 725 requests in a webapplication that has been open 3 weeks which I have made myself
and now to get a good view of amount requests with different status levels such as
Simple db example:
tblRequests
id,
name,
fk_tblStatus_id
...
tblStatus
id (the id defines the statuslevel)
status_description
id   status_description
1    new (not viewed)
2    viewed
3    pending
4    closed
I have written this following statement which is fairly simple and almost produces what I want but not fully and I have tried a few different ways without sucseeding in getting what I want
SELECT
count
(r.status_id) As "Cases", s.status
FROM tblRequests r Join tblStatus s ON r.status_id = s.id
GROUP BY s.status
this gives me the following result:
AMT STATUS
600 closed
25 new
95 pending
5 viewed
what I want it to produce is a result that sorts after id in tblStatus and sum a total result it would look
something like this
AMT STATUS
25 new
5 viewed
95 pending
600 closed
725 Total
Can anyone help me with this please....Help

Viewing 9 posts - 1 through 8 (of 8 total)

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

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

The ROLLUP below will work, however it breaks when additional columns (status.id) are added for ordering. Totalling should probably be a part of reporting not data retrival.

create table #requests (id int identity, name varchar(50), statusID int)

create table #status (id int identity, description varchar(50))

INSERT INTO #status (description)

select 'new' UNION ALL

select 'viewed' UNION ALL

select 'pending' UNION ALL

select 'Closed'

INSERT INTO #requests (name, statusid)

select 'joe', 1 UNION ALL

select 'joe', 2 UNION ALL

select 'joe', 3 UNION ALL

select 'joe', 4 UNION ALL

select 'joe', 1

select * from #requests

SELECT

count(r.statusid) As "Cases", isnull(s.description, 'TOTAL')

FROM #requests r Join #status s ON r.statusid = s.id

GROUP BY s.description with rollup

good luck

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Will that put it in the order the OP requested?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Add an extra line

ORDER BY s.ID

N 56°04'39.16"
E 12°55'05.25"

Magnus T Valued Member Points: 54 More actions · Answer 4

Thanks,

your right daryl totalling should be part of reporting and im just gonna sum the result in my VB-Code instead.

but that puts me back on square one because I dont get the correct order I want it in from the database.

And adding the line

ORDER BY s.id

doesnt work the Column name 's.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause, tried that before I posted my question.

Any one else?

/Magnus

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Peter,

Now you know why I asked... was pretty sure the Order By wouldn't do it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Haven't tested it but learned a long time ago never to mix aggragation and formatting in a single select...

SELECT d.Cases,d.Status

FROM

(

SELECT

count(r.status_id) As "Cases", s.status,r.status_id

FROM tblRequests r Join tblStatus s ON r.status_id = s.id

GROUP BY s.status,r.status_id

) d

ORDER BY d.status_id

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Magnus T Valued Member Points: 54 More actions · Answer 7

Thanks a bunch Jeff,

That did the trick....I should have known this but I guess some time you forget things...

/Magnus

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Great... thanks for the feedback, Magnus...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AMT	STATUS
600	closed
25	new
95	pending
5	viewed

AMT	STATUS
25	new
5	viewed
95	pending
600	closed
725	Total