SQL Query- Question

[dupati1]

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

[WebJoe]

I woulnd't know of one single query to get all the results you asked for. With two seperate queries, however, would something like

SELECT Date, SUM(Totalsales) AS Totalsales, SUM(Amount) AS Amount FROM Balance WHERE ... GROUP BY Date

and

SELECT SUM(Totalsales) AS TotalSales, SUM(Amount) As TotalAmount FROM Balance WHERE ...

prpbably answer your question.

[edited by: WebJoe at 4:31 pm (utc) on Nov. 7, 2003]

[incywincy]

something along the lines of the following might work:

select distinct date, sum(Totalsales) TotalSales,
sum(Amount) sum from Balance group by date order by date;

this query will give you the table, grouping your data by date.

then you could have seperate queries

for Total Sales:
select sum(Totalsales) from Balance;

for Total Amount:
select sum(Amount) from Balance;

these will give you data for the entire date range in your table, you will have to add a where clause to only select a particular date range

hth

[dupati1]

Thanks Guys,

I got the query working.

VJ