asked on

SUM of SQL statement

I am trying to create a SUM of the records queried from the database.

My original statement to get the individual records, which i am displaying with a repeat region is:

SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchInfoID, tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility, tblchurchinfo.dateID, tblchurchinfo.churchID, tblmonths.monthID, tblmonths.monthLabel
FROM tblchurch, tblchurchinfo, tblmonths
WHERE tblchurchinfo.dateID = dateVar AND tblchurchinfo.churchID = tblchurch.churchID AND tblchurchinfo.dateID = tblmonths.monthID AND tblchurchinfo.yearID = yearVar
ORDER BY tblchurch.churchName ASC

My SUM SQL statement is:

SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchInfoID, SUM(tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility), tblchurchinfo.dateID, tblchurchinfo.churchID, tblmonths.monthID, tblmonths.monthLabel
FROM tblchurch, tblchurchinfo, tblmonths
WHERE tblchurchinfo.dateID = dateVar AND tblchurchinfo.churchID = tblchurch.churchID AND tblchurchinfo.dateID = tblmonths.monthID AND tblchurchinfo.yearID = yearVar
ORDER BY tblchurch.churchName ASC

however, this is not working.

I am currently getting the data into a table repeating for all the info in the query. It seems it would be just as simple to add the column totals from the repeat region that is created. Is it possible to SUM a Do/While loop?

ralmada

something like below?

If not please post some sample data and the expected result.

SELECT 	tblchurch.churchID, 
	tblchurch.churchName, 
	tblchurchinfo.churchInfoID, 
	SUM(tblchurchinfo.churchAttendance + tblchurchinfo.churchSSAttendance + tblchurchinfo.churchResponsibility), 
	tblchurchinfo.dateID, 
	tblchurchinfo.churchID, 
	tblmonths.monthID, 
	tblmonths.monthLabel

FROM tblchurch, tblchurchinfo, tblmonths
WHERE 	tblchurchinfo.dateID = dateVar AND 
	tblchurchinfo.churchID = tblchurch.churchID    AND 
	tblchurchinfo.dateID = tblmonths.monthID AND 
	tblchurchinfo.yearID = yearVar

group by tblchurch.churchID, 
	tblchurch.churchName, 
	tblchurchinfo.churchInfoID, 
	tblchurchinfo.dateID, 
	tblchurchinfo.churchID, 
	tblmonths.monthID, 
	tblmonths.monthLabel

Open in new window

Guy Hengel [angelIII / a3]

if your joins result in "duplicates", you need to either use DISTINCT or GROUP BY... but before doing the SUM.

so, you might need to subquery for the SUM ...
however, your second select seems wrong syntax?

Ray Paseur

What do you mean when you say it is "not working?" What did you expect that you did not get?

axessJosh

ASKER

Actually, i've got it working locally, but when i put on my public server it gave me the error requiring a 'group by' clause. Now, on my public server, it totals on the first record it finds, not all the records.

works fine locally.

here is my SQL:

$query_rsInfoSum1 = sprintf("SELECT SUM(tblchurchinfo.churchAttendance) as total, tblchurchinfo.dateID, tblchurchinfo.yearID FROM tblchurchinfo WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.yearID = %s GROUP BY tblchurchinfo.churchAttendance", GetSQLValueString($dateVar_rsInfoSum1, "int"),GetSQLValueString($yearVar_rsInfoSum1, "int"));

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial