Assigning a fieldname with rollup

Question

Post reply

Assigning a fieldname with rollup

znetznet

SSC Journeyman

Points: 83
More actions
April 16, 2013 at 11:26 am

#276661

I have two tables that have racing data, and I need to add up the number of cancelled races and make it my aliased field name (in this case as you see the query below numCancelled, meaning number of races cancelled):
(select a.eid, count(*) as numCancelled from
(select ev4.eid,f.nvid, f.perfdate, f.racenumber from Finish f (nolock)
join event ev4 (nolock) on ev4.eid = f.eid where
f.NoRace = 'true' and f.PerfDate = '2013-04-11') a group by a.eid with rollup)
....the resultset here just lists all the eids (event ids) then the rollup total (in this case it is referred to a NULL in that eid column), but I need my numCancelled field to contain that grand total 7, as right now it comes back as 1 when I run this subquery in my stored procedure.
??
Z

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

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

If you post table script and script to insert some sample data that will be very helpful.

znetznet SSC Journeyman Points: 83 More actions · Answer 2

DevDB (4/16/2013)
If you post table script and script to insert some sample data that will be very helpful.

The DDL for the tables involved isnt the issue, as the correct data is coming back, its more of how to get the rollup to place the grand total in a field I can use. Here is an extended example of the statement, whereas I am replacing the NULL with a name called Grand Total:

( select isnull(convert(VARCHAR,a.eid),'GRAND TOTAL') AS eid,

COUNT(*) as numCancelled

from (select ev4.eid,f.nvid, f.perfdate, f.racenumber

from Finish f (nolock) join event ev4 (nolock)

on ev4.eid = f.eid

where f.NoRace = 'true' and f.PerfDate = '2013-04-11' and f.NvId='254') a

group by a.eid with rollup)

the result being:

Eidnumcancelled

1000601

1000611

1000621

1000631

1000641

1000651

1000661

GRAND TOTAL7

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

znetznet (4/16/2013)
DevDB (4/16/2013)
If you post table script and script to insert some sample data that will be very helpful.
The DDL for the tables involved isnt the issue, as the correct data is coming back, its more of how to get the rollup to place the grand total in a field I can use. Here is an extended example of the statement, whereas I am replacing the NULL with a name called Grand Total:
( select isnull(convert(VARCHAR,a.eid),'GRAND TOTAL') AS eid,
COUNT(*) as numCancelled
from (select ev4.eid,f.nvid, f.perfdate, f.racenumber
from Finish f (nolock) join event ev4 (nolock)
on ev4.eid = f.eid
where f.NoRace = 'true' and f.PerfDate = '2013-04-11' and f.NvId='254') a
group by a.eid with rollup)
the result being:
Eidnumcancelled
1000601
1000611
1000621
1000631
1000641
1000651
1000661
GRAND TOTAL7

Not actually interested in the DDL. More interested in "readily consumable data" which will require some DDL. Please see the first link in my signature line below to get better help more quickly. Thanks.

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

sqldriver SSChampion Points: 10440 More actions · Answer 4

I think the question is if there's a way to have something other than NULL next to the rollup count? That's actually pretty interesting, if possible.

id Records

NULL 5357

1000011

1000021

1000031

1000041

1000051

1000061

1000071

1000081

1000091

As a weird side note, when i selected top 10 id, the rollup counted all ids in the column.

select top 10 id, count(*) as Records

from table

group by id with rollup

order by id

OR

select top 10 id, count(id) as Records

from table

group by id with rollup

order by id

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 5

It's a hack but you could always do:

select case when ID is null then 'CountOf' else cast(id as varchar(20)) end as ID, countof

from

(

select id, COUNT(*) as countof

from

(values(100001,1),

(100002,1),

(100003,1),

(100004,1),

(100005,1),

(100006,1),

(100007,1),

(100008,1),

(100009,1)) as Val (id, records)

group by id with rollup

) source

sqldriver SSChampion Points: 10440 More actions · Answer 6

I think you'll find this article helpful:

http://sqlandme.com/2011/07/07/sql-server-tsql-group-by-with-rollup/