Link to home
Create AccountLog in
Avatar of jcm4101
jcm4101Flag for Australia

asked on

Column total in sql datasource

Hi Experts..

I have a gridview that is bound to a sql datasource.

I need to sum up one colum in the datasource.

The gridview is paged (as the number of returned rows is many), so I can't just the columns via a row_databound event, because I just get the sum of the column that is in that particular page of the datagrid.

So, I have a datasource that returns say 20 rows.
There is one column called 'days'
I need to add up the values in this column for the entire 20 rows returned, not just the first page of the datagrid.

Can someone point me in the right direction with this one please.

Thanks in advance

D
Avatar of iopen
iopen
Flag of Australia image

Get your total in your SQL with SUM

SELECT SUM(column) as "Total" FROM datasource
Avatar of jcm4101

ASKER

Thanks for the swift response iopen, but I think I'd have to execute a seperate query for that, and I'm trying to avoid multiple queries..

Is this right?

Ta
D
You should be able to structure your query to include the SUM of a column, that is why you use the - AS "TOTAL"

HTH
A quick example would be

SELECT columnInfo, column, SUM(column) AS "total" FROM Table
Avatar of jcm4101

ASKER

Thanx again iopen, I'm generally  familiar with structuring SQL statements (MySQL) , and am always looking to improve my skills in this area,  but constantly receive an error complaining about mixing of GROUP columns.

For example..(Simple query..)
SELECT luname, sum(ldays) as days FROM `Leave`.`Applications` WHERE luname = 'daveb';

..gives me an error, probably because it needs a group by clause..

FYI, here's the actual query I wish to add the sum() function to..


SELECT a.userLongName as Name, b.lstart as Start, b.lend as End, b.ldays as Days, sum(ldays) as Dur FROM traintrack.user a INNER JOIN Leave.Applications b ON a.userLongName = b.lname WHERE a.userActive = '1' AND (b.lstart > date_sub(curdate(), interval 365 day) AND b.lend <= curdate()) AND b.ltype = 'LWOP' AND b.luname LIKE @name AND b.lbranch LIKE '%' ORDER BY 'b.lstart' ASC

Open in new window

Avatar of jcm4101

ASKER

..Sorry, the query didn't insert as I expected..
See below..

SELECT a.userLongName as Name, b.lstart as Start, b.lend as End, b.ldays as Days, sum(ldays) as Dur FROM traintrack.user a INNER JOIN Leave.Applications b ON a.userLongName = b.lname WHERE a.userActive = '1' AND (b.lstart &gt; date_sub(curdate(), interval 365 day) AND b.lend &lt;= curdate()) AND b.ltype = 'LWOP' AND b.luname LIKE @name AND b.lbranch LIKE '%' ORDER BY 'b.lstart' ASC
Instead of sum(ldays)  it should be sum(b.ldays)
Avatar of jcm4101

ASKER

Yes, thanks for that, but the query still requires a group by clause in order for the sum() to work.

Lets say I have a table with 3 columns..(Leave.Applications)

ID                name                  days
01              daveb                    3
01              gregn                     2
01              daveb                    3
01              paulat                    3
01              brooke                   3


I want to retrieve all records PLUS a total of the days column (ie, 14)

My query without the totals feature would be..

SELECT name, days from Leave.Applications;

So, how are you saying I amend this query to also return a column that has the total of the days column also?

What if I amended the query to be..
SELECT name, days from Leave.Applications WHERE lname = 'daveb';

How would I do it now?

Thanks again for youe help...

D
Avatar of jcm4101

ASKER

Sorry, Column ID should have incremented  :-)
ASKER CERTIFIED SOLUTION
Avatar of Stephan_Schrandt
Stephan_Schrandt
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yes a separate SELECT to do the total SUM would be required with your data as Stephan shows above.

Beat to it while I was out to lunch :)
Avatar of jcm4101

ASKER

Thanx guys, great help.

All now working well..

Final SELECT =
Select a.userLongName as Name, b.lstart as Start, b.lend as End, b.ldays as Days, (SELECT (Sum(b.ldays)) FROM traintrack.user a INNER JOIN Leave.Applications b ON a.userLongName = b.lname WHERE a.userActive = '1' AND (b.lstart > date_sub(curdate(), interval 365 day) AND b.lend <= curdate()) AND b.ltype = 'Annual' AND b.luname LIKE 'daveb' AND b.lbranch LIKE '%') as Total FROM traintrack.user a INNER JOIN Leave.Applications b ON a.userLongName = b.lname WHERE a.userActive = '1' AND (b.lstart > date_sub(curdate(), interval 365 day) AND b.lend <= curdate()) AND b.ltype = 'Annual' AND b.luname LIKE 'daveb' AND b.lbranch LIKE '%'

Open in new window


Once again, thanks..

D