Solved

Total row in an SQL query

Posted on 2010-11-15
14
410 Views
Last Modified: 2012-05-10
Hi Guys,

Someone last week helped me create a query which is working fine.

My boss now wishes me to have a total row across the bottom.

Please can someone advise if this can be done and help do it for me.

I have attached two separate bits of code and an image of the results for you to work from.


Notes:  This query is ran through an alert server (Orbis) so does need to be generated in the query.

Select RSM,sum(PTD)as PTD,sum(case DATEPART(month,getdate())
			when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10 then TL10
            when 11 then TL11
            WHEN 12 THEN TL12 
            END) as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
group by rsm






Select custcode,custname,PTD,
            case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12 
            END as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
order by custcode

Open in new window

sqlresults.jpg
0
Comment
Question by:dexterhome
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34135400
let's work on the first one:
Select RSM,sum(PTD)as PTD,sum(case DATEPART(month,getdate())
                        when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10 then TL10
            when 11 then TL11
            WHEN 12 THEN TL12 
            END) as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
group by rollup(rsm)

Open in new window


http://msdn.microsoft.com/en-us/library/ms177673.aspx
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34135403
if you use "older" sql server version:
Select RSM,sum(PTD)as PTD,sum(case DATEPART(month,getdate())
                        when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10 then TL10
            when 11 then TL11
            WHEN 12 THEN TL12 
            END) as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
group by rsm
with rollup

Open in new window

0
 
LVL 5

Author Comment

by:dexterhome
ID: 34135475
Angellll - Thanks for that.  It works correctly.  Can we make the row say TOTAL instead of NULL.

AG-STD      54653.37      189315.76
AH-STD      9297.27      19086.81
DH-STD      8374.34      27897.28
DW-STD      29041.64      47135.43
SALES-OFFICE      111490.57      94575.52
WH-STD      45405.91      66144.94
NULL      258263.1      444155.74
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34135520
yes, presuming that the RSM field as such is never being NULL, and never has 'TOTAL' as value it self:
Select ISNULL(RSM, 'TOTAL'),sum(PTD)as PTD,sum(case DATEPART(month,getdate())

                        when 1 then TL1

            when 2 then TL2

            when 3 then TL3

            when 4 then TL4

            when 5 then TL5

            when 6 then TL6

            when 7 then TL7

            when 8 then TL8

            when 9 then TL9

            when 10 then TL10

            when 11 then TL11

            WHEN 12 THEN TL12 

            END) as PLY

from PYR_VIEW_RSM_DATA

--where rsm = 'AG-STD'

group by rsm

with rollup

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34135525
On behalf of angelIII

use IsNull

IsNull(RSM,'Total')RSM
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34135534
sorry angelIII

comment cross over
0
 
LVL 5

Author Comment

by:dexterhome
ID: 34135596
That works just as required. - Thanks - Thats one done.

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 34139993
You *may* be able to use COMPUTE on the second one, but it does generate separate result sets.  Don't know if Orbis can handle two result sets.

 Select custcode,custname,PTD,
            case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12
            END as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
order by custcode
compute sum(PTD),
    sum(case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12
            END)


If not, you could add "dummy" GROUPing to the query to allow ROLLUP to be specified, although that would slow down the query somewhat.
0
 
LVL 5

Author Comment

by:dexterhome
ID: 34143607
ScottPletcher:  
Thanks for that but (as you thought) Orbis will not work with two result sets.

How would I work the dummy grouping. - please advise.


0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 34147231
Something like this should do it:

Select ISNULL(custcode, 'Total') AS custcode,
            MAX(custname) AS custname,SUM(PTD) AS PTD,
            SUM(case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12
            END) as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
group by ISNULL(custcode, 'Total') with rollup
order by custcode
0
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 250 total points
ID: 34148480
In MySQL UNION is a valid connector for combining 2 SELECT
commands into one.

I would presume that Orbis would allow a command like this
to be executed:

Select custcode,custname,PTD,
            case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12
            END as PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'
order by custcode
UNION
Select 'TOTALS',' ',SUM(PTD) AS PTD,
            SUM(case DATEPART(month,getdate())
            when 1 then TL1
            when 2 then TL2
            when 3 then TL3
            when 4 then TL4
            when 5 then TL5
            when 6 then TL6
            when 7 then TL7
            when 8 then TL8
            when 9 then TL9
            when 10      then TL10
            when 11 then TL11
            WHEN 12 THEN TL12
            END) AS PLY
from PYR_VIEW_RSM_DATA
--where rsm = 'AG-STD'


0
 
LVL 5

Author Comment

by:dexterhome
ID: 34154275
scottpletcher - That statement appears to add a new row to the top that shows a NULL code, and then any customer name, then the totals. - Please see the code snipet below


wolfgang 93:  The union statment can be used in Orbis to pull the queries together - the code you provided just produces an error though.
NULL	YORKSHIRE CARAVANS	62539.16	189315.76

11076	ATTWOOLLS CAMPING	386.9	26831.6

11096	THE BIG OUTDOORS COMPANY LTD	97.2	31.58

11148	BOLSOVER CARAVANS	0	4190.48

11222	CAMPING INTERNATIONAL (G'HAM)	5051.24	462.13

11224	CAMPING & GENERAL	362	762.9

11252	CARAVAN COMPANY (LEICS)	0	0

11261	CARAVANSTUFF 4U LTD	7822.13	6472.91

11274	CATTERICK CARAVANS	3724.32	-4549.74

11277	CDS (SUPERSTORES INT) LTD	0	0

11280	CHARLIES STORES LTD	0	0

11294	CHICHESTER CARAVANS BIRMINGHAM	139.49	732.81

11297	CHICHESTER CARAVANS WORCESTER	0	0

11339	CRAVENWOOD LTD	0	0

11358	DAVAN CARAVANS	242.48	-82.84

11427	EAST COAST LEISURE LTD	0	-225.51

11499	FRONTIER LEISURE UK LTD	467.94	19066.57

11597	HIGHBRIDGE CARAVANS	0	549.95

11617	HOMESTEAD CARAVAN CENTRE	-98.5	13536.33

11658	J.R.LEISURE WORLD	11614.39	448.24

11660	JACKSONS OF OLD ARLEY	1161.8	22251.18

11668	JEFF BOWEN AWNINGS	0	0

11701	KIMBERLEY CARAVAN CENTRE LTD	-525.56	1042.39

11750	LOWDHAM LEISUREWORLD (NOTTM)	0	290.97

11751	LOWDHAM LEISUREWORLD (H'FIELD)	355.6	851.24

11884	NORWICH CAMPING & LEISURE	31.23	52794.01

11886	NORWICH CARAVANS (HEVINGHAM)	0	0

11926	OUTDOOR EXPERIENCE LTD	0	0

12018	READ CARAVANS (BLACKBURN)	678.14	194.92

12019	READ CARAVANS (LEEDS)	0	2.82

12059	ROPER CARAVANS (WASHINGTON)	0	0

12060	ROPER CARAVANS (CATTERICK)	341.08	-33.05

12087	SALOP LEISURE LTD	428.65	427.61

12112	SK CAMPING	157.6	17041.94

12194	SUTTERTON C & C	161.24	0

12301	UNITED BRITISH CARAVANS LTD	0	0

12315	W. YEOMANS (CHESTERFIELD) LTD	0	-619.49

12320	WANDAHOME (KNOTTINGLY)	10471.43	14566.98

12321	WANDAHOME	407.67	327.56

12327	WAUDBYS	5265.92	2202.43

12345	WHITE ARCHES CARAVAN	3527	8369.96

12367	YORKSHIRE CARAVANS	8976.15	-30.99

20194	DISCOVER HEAD OFFICE	1291.62	1407.87

20308	GOLDMINE TEST ACCOUNT	0	0

Open in new window

0
 
LVL 5

Author Comment

by:dexterhome
ID: 34154290
Wolfgang 93:  I have checked your statment and noticed the  "order by" clause which does not like to play with the "union" statement.

I have now removed this and it appears to work correctly.

I will double check it in Orbis and advise again.
0
 
LVL 5

Author Closing Comment

by:dexterhome
ID: 34154421
Thanks for all your help guys.
All working in Orbis and emails look good to me.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now