Understanding query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Understanding query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-06 : 09:59:56

Someone else wrote the code so I'm trying to understand the '' as reg, '' as Dist and '' as Doc in the select statement below.

So is this correct by saying select nothing for reg, dist and doc so this will give me the grand total (which is Nation)?

Here's the query and below are the table results (my output from the query)

alter procedure [dbo].[GetT16DistDoc] '200'
@dist varchar (3)

AS

select '' as reg, regionname, Area, '' as Dist, '' as DOC,

SUM(totpenfodds) AS 'totpenfodds',
SUM(pendndds) AS 'pendndds',
SUM(totpenfodds - pendndds) AS pendinfo,
SUM(pendfo300) AS 'pendfo300',
SUM(penddds300) AS 'penddds300',
SUM(pend250) AS 'pend250',
SUM(pend300) AS 'pend300',
SUM(pend351) AS 'pend351',
SUM(pend400) AS 'pend400'

FROM T16Report
where dist =@dist
group by reg, regionname, area

union

select reg, regionname, area, Dist, DOC,
SUM(totpenfodds) AS 'totpenfodds',
SUM(pendndds) AS 'pendndds',
SUM(totpenfodds - pendndds) AS pendinfo,
SUM(pendfo300) AS 'pendfo300',
SUM(penddds300) AS 'penddds300',
SUM(pend250) AS 'pend250',
SUM(pend300) AS 'pend300',
SUM(pend351) AS 'pend351',
SUM(pend400) AS 'pend400'
FROM T16Report
where dist =@dist
group by reg, regionname, area, dist, doc
order by reg, regionname, area, dist, doc


Reg	Regname	Area	Dist	Doc	Pendinfo  Pendfo300 Pend250
	PHIL	01			 1236	     6	       2
C	PHIL	01	200	009	  352	     4	       4
C	PHIL	01	200	200	  884	     2	       0

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-06 : 10:04:17

[code]
select reg, regionname, area, Dist, DOC,
SUM(totpenfodds) AS 'totpenfodds',
SUM(pendndds) AS 'pendndds',
SUM(totpenfodds - pendndds) AS pendinfo,
SUM(pendfo300) AS 'pendfo300',
SUM(penddds300) AS 'penddds300',
SUM(pend250) AS 'pend250',
SUM(pend300) AS 'pend300',
SUM(pend351) AS 'pend351',
SUM(pend400) AS 'pend400'
FROM T16Report
where dist =@dist
group by reg, regionname, area, dist, doc WITH CUBE
order by reg, regionname, area, dist, doc

[/code]

Will do the same thing.

Vaibhav T

If I cant go back, I want to go fast...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-06 : 10:07:09

Thanks but when I tried yours I got the error message

'CUBE' is not a recognized built-in function name.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-06 : 10:10:21

Ohh sorry...
Removed brackets ()
I edited the post.

Vaibhav T

If I cant go back, I want to go fast...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-06 : 10:19:54

Did that now getting incorrect syntax near reg

select reg, regionname, area, Dist, DOC,
SUM(totpenfodds) AS 'totpenfodds',
SUM(pendndds) AS 'pendndds',
SUM(totpenfodds - pendndds) AS pendinfo,
SUM(pendfo300) AS 'pendfo300',
SUM(penddds300) AS 'penddds300',
SUM(pend250) AS 'pend250',
SUM(pend300) AS 'pend300',
SUM(pend351) AS 'pend351',
SUM(pend400) AS 'pend400'
FROM T16Report
where dist ='200'
group by CUBE reg, regionname, area, dist, doc
order by reg, regionname, area, dist, doc

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-06 : 10:44:16

So is this right?

'' as reg, '' as Dist and '' as Doc in the select statement will give put nothing in those fields so I can get my grand total? It comes out as this but want to understand it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-06 : 11:09:14

Yes, it is your grand total.

N 56°04'39.26"
E 12°55'05.63"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-06 : 11:17:59

Thanks again Peso! Trying to understand what I'm using to get as good as you guys...I know I have a long way to go!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 02:11:48

In your original post
First Query is giving you grand total and second query giving total of group.
But If you are using my query "Group By Cube" will give you extra row
which is coming from your first query before union.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources