Sum of Sum - 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)

Sum of Sum

Author

Topic

scozzese
Starting Member

12 Posts

Posted - 2003-12-22 : 13:30:37

I've the following query that works properly:
*********************************************
SELECT
Sum(TABLE_A.no_hour)+Sum(TABLE_A.no_min)/60 AS TotalHour,
(Sum(TABLE_A.no_ore)+Sum(TABLE_A.no_min)/60)*TABLE_B.unit_fare as As TotalAmount
FROM
((TABLE_C INNER JOIN TABLE_A
ON TABLE_C.cod_progr_attivita = TABLE_A.cod_progr_attivita)
INNER JOIN TABLE_D ON TABLE_C.cod_dipendente = TABLE_D.cod_dipendente)
INNER JOIN TABLE_B ON
(TABLE_D.cod_centro_di_costo = TABLE_B.cod_centro_di_costo)
AND ( month(TABLE_B.data_riferimento) = month(TABLE_C.data_riferimento)
AND year(TABLE_B.data_riferimento) = year(TABLE_C.data_riferimento) )
**********************************************
Is it possible to get in one query sum(TotalHour),sum(TotalAmount)?
Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-22 : 13:33:53

You could treat your existing query as a derived table and sum on it:


SELECT SUM(TotalHour) GrandTTLHour, SUM(TotalAmount) GrandTTLAmount
FROM
(
	SELECT 
	Sum(TABLE_A.no_hour)+Sum(TABLE_A.no_min)/60 AS TotalHour,
	(Sum(TABLE_A.no_ore)+Sum(TABLE_A.no_min)/60)*TABLE_B.unit_fare as As TotalAmount
	FROM 
	((TABLE_C INNER JOIN TABLE_A 
	ON TABLE_C.cod_progr_attivita = TABLE_A.cod_progr_attivita) 
	INNER JOIN TABLE_D ON TABLE_C.cod_dipendente = TABLE_D.cod_dipendente) 
	INNER JOIN TABLE_B ON
	(TABLE_D.cod_centro_di_costo = TABLE_B.cod_centro_di_costo) 
	AND ( month(TABLE_B.data_riferimento) = month(TABLE_C.data_riferimento) 
	AND year(TABLE_B.data_riferimento) = year(TABLE_C.data_riferimento) )
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources