Sql 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sql Query

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2007-10-08 : 12:22:36

Hi Guys,
I have this query below. I want to get the GrandTotal of tblspot.rateactual at the end of this result. Can anybody help me how to do this.? Appreciate your help
Thx

SELECT distinct tblNetworks.description AS NETWORK,'$' + CONVERT(varchar(12), CONVERT(money, tblSpot.rateActual, 1)) AS ACTUALRATE
, tblAdvertiser.description AS ADVERTISER, tblAgency.description
AS AGENCY, tblContract.pkid AS CONTRACTID,tblContract.estimatenumber AS STATIONNUMBER, tblInvoice.invoiceNumber FROM tblContract INNER JOIN tblSpot ON tblContract.pkid = tblSpot.fkContract
INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid INNER JOIN tblNetworks ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON
tblSpot.fkAgency = tblAgency.pkid INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber
WHERE (tblSpot.dateActual BETWEEN '7/30/2007' AND '8/26/2007')

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-08 : 14:45:01

You can do it at the front end if you are binding these results to some control?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-08 : 14:45:29

see this link:

http://www.databasejournal.com/features/mssql/article.php/3112381

vk18
Posting Yak Master

146 Posts

Posted - 2007-10-11 : 19:20:08

quote:
Originally posted by vk18

Hi Guys,
I have this query below. I want to get the GrandTotal of tblspot.rateactual at the end of this result. Can anybody help me how to do this.? Appreciate your help
Thx

SELECT distinct tblNetworks.description AS NETWORK,'$' + CONVERT(varchar(12), CONVERT(money, tblSpot.rateActual, 1)) AS ACTUALRATE
, tblAdvertiser.description AS ADVERTISER, tblAgency.description
AS AGENCY, tblContract.pkid AS CONTRACTID,tblContract.estimatenumber AS STATIONNUMBER, tblInvoice.invoiceNumber FROM tblContract INNER JOIN tblSpot ON tblContract.pkid = tblSpot.fkContract
INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid INNER JOIN tblNetworks ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON
tblSpot.fkAgency = tblAgency.pkid INNER JOIN tblInvoice ON tblSpot.fkInvoiceNumber = tblInvoice.invoiceNumber
WHERE (tblSpot.dateActual BETWEEN '7/30/2007' AND '8/26/2007')

Hi Guys,

I tried using WITH ROLLUP but the grand total is coming on top. how can we get at the bottom.? any idea
THX

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 20:03:56

You can add a GROUPING statement in your SELECT clause and then order on it, the grand total will then be on the bottom. SELECT 'sort1' = GROUPING(tblNetworks.description) FROM etc
order by sort1.

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 02:49:14

quote:
Originally posted by jimf

You can add a GROUPING statement in your SELECT clause and then order on it, the grand total will then be on the bottom. SELECT 'sort1' = GROUPING(tblNetworks.description) FROM etc
order by sort1.

Jim

I think GROUPING can be used only if you use ROLLUP or CUBE

Madhivanan

Failing to plan is Planning to fail

vk18
Posting Yak Master

146 Posts

Posted - 2007-10-12 : 12:29:12

quote:
Originally posted by jimf

You can add a GROUPING statement in your SELECT clause and then order on it, the grand total will then be on the bottom. SELECT 'sort1' = GROUPING(tblNetworks.description) FROM etc
order by sort1.

Jim

Hi,

I tried this, It doesn't work. Any other idea.?
Thx

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-12 : 12:47:03

Not without seeing the syntax for what you tried that didn't work.

DECLARE @Tbl TABLE (Col1 int IDENTITY(1,1),Col2 varchar(50))
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Angie'
INSERT INTO @tbl SELECT 'Angie'
INSERT INTO @tbl SELECT 'Angie'

SELECT [sort1] = GROUPING(Col2)
,Col2
,[Total] = SUM(Col1)

FROM @tbl
GROUP BY Col2
WITH ROLLUP
ORDER BY sort1

vk18
Posting Yak Master

146 Posts

Posted - 2007-10-12 : 13:48:43

quote:
Originally posted by jimf

Not without seeing the syntax for what you tried that didn't work.

DECLARE @Tbl TABLE (Col1 int IDENTITY(1,1),Col2 varchar(50))
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Jim'
INSERT INTO @tbl SELECT 'Angie'
INSERT INTO @tbl SELECT 'Angie'
INSERT INTO @tbl SELECT 'Angie'

SELECT [sort1] = GROUPING(Col2)
,Col2
,[Total] = SUM(Col1)

FROM @tbl
GROUP BY Col2
WITH ROLLUP
ORDER BY sort1

Hi,
I am not using any stored procs, it is a plain simple query.

SELECT distinct grouping(tblNetworks.description AS NETWORK,'$' + CONVERT(varchar(12),CONVERT(money,sum(tblSpot.rateActual),1)) AS TOTALAMOUNT) FROM tblContract
INNER JOIN tblSpot ON tblContract.pkid = tblSpot.fkContract INNER JOIN tblAdvertiser ON tblContract.fkAdvertiser = tblAdvertiser.pkid INNER JOIN
tblNetworks ON tblSpot.fkNetwork = tblNetworks.pkid INNER JOIN tblAgency ON tblSpot.fkAgency = tblAgency.pkid WHERE tblSpot.fkContractType = 'unwired' group by tblnetworks.description

I have like couple of tables that i am joining as you can see above.
is it possible with this.?
Thx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-12 : 14:06:01

Where are you outputting these results? As already mentioned, the easiest and also the most efficient way to do totaling is at the client. What client tool is displaying these results?

- Jeff
http://weblogs.sqlteam.com/JeffS

vk18
Posting Yak Master

146 Posts

Posted - 2007-10-12 : 14:12:31

quote:
Originally posted by jsmith8858

Where are you outputting these results? As already mentioned, the easiest and also the most efficient way to do totaling is at the client. What client tool is displaying these results?

- Jeff
http://weblogs.sqlteam.com/JeffS

I am displaying these results on the DataGrid
Thx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-12 : 14:13:53

Do you know, or have you researched, how to do grand totals for an ASP.NET datagrid? It's very, very easy if you know ASP.NET.

see:

http://aspalliance.com/494
http://www.dotnetspider.com/kb/Article944.aspx
http://www.dotnetjunkies.com/Tutorial/2F527E21-A6C5-497A-8B56-4150BDAF711D.dcik

etc ...

google will help you easily find more. The key is know when to do things like this in SQL versus at the client. Grand totals 99% of the time should be done at the client.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources