SQL Query - question - 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

SQL Server Development (2000)

SQL Query - question

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-07 : 09:24:20

Hi all,

I am using SQL server 2000 as backend and ASP as frontend. I have a table named "Balance" with the following columns:

Date Totalsales Amount
09/01/2003 1 300
09/01/2003 2 400
09/02/2003 3 550
09/03/2003 1 300
09/03/2003 1 300

How should i write a SQL query to display the result on the ASP page in the following pattern

************************************************
Date Totalsales Amount
09/01/2003 3 700
09/02/2003 3 1250
09/03/2003 2 1850

Total sales: 8
Total Amount:1850
*************************************************

Thanks in advance.

VJ

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-07 : 09:43:46

select
dateadd(dd,datediff(dd,0,[date]),0) as 'Date',
sum(TotalSales) as 'Totalsales',
sum(Amount) as 'Amount'
from
dupati1
group by
dateadd(dd,datediff(dd,0,[date]),0)

... You can figure the 8 and 1850, I'm sure ...

EDIT: I completely misread the question ... sorry

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-07 : 09:49:23

Look up WITH CUBE, or ROLLUP in BOL...buch of examples...

Though I'm not sure you can do running totals...


DECLARE @myTable99 TABLE ([Date] Datetime, TotalSales int, Amount money)

INSERT INTO @myTable99 ([Date], TotalSales, Amount)
SELECT '09/01/2003', 1, 300 UNION ALL
SELECT '09/01/2003', 2, 400 UNION ALL
SELECT '09/02/2003', 3, 550 UNION ALL
SELECT '09/03/2003', 1, 300 UNION ALL
SELECT '09/03/2003', 1, 300

  SELECT [Date], SUM(TotalSales), SUM(Amount) 
    FROM @myTable99 
GROUP BY [Date] WITH ROLLUP

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-07 : 09:53:09

Jay,

What does that do?


DECLARE @myTable99 TABLE ([Date] Datetime, TotalSales int, Amount money)

INSERT INTO @myTable99 ([Date], TotalSales, Amount)
SELECT '09/01/2003', 1, 300 UNION ALL
SELECT '09/01/2003', 2, 400 UNION ALL
SELECT '09/02/2003', 3, 550 UNION ALL
SELECT '09/03/2003', 1, 300 UNION ALL
SELECT '09/03/2003', 1, 300

  SELECT [Date], SUM(TotalSales), SUM(Amount) 
    FROM @myTable99 
GROUP BY [Date] WITH ROLLUP

  SELECT  DATEADD(dd,DATEDIFF(dd,0,[date]),0) 	AS 'Date'
    	, SUM(TotalSales) 			AS 'Totalsales'
	, SUM(Amount) 				as 'Amount'
    FROM	  @myTable99
GROUP BY  dateadd(dd,datediff(dd,0,[date]),0)

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-07 : 09:57:31

Didn't Jeff alread give you the anser?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30369

Brett

8-)

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-07 : 12:50:27

Thanks Guys,

I got the query working.

VJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources