sum( case when..) - 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 2008 Forums

Transact-SQL (2008)

sum( case when..)

Author

Topic

titan_ae78
Starting Member

9 Posts

Posted - 2009-07-01 : 07:40:39

Hi,
I'm using this SQL query with Access but it doesn't work with sql server.

sum(iif(PrimaNota.Tipo=false,PrimaNota.dareAvere,0)) as DARE

So I've changed iif instruction with CASE WHEN THEN ELSE END, like this:

(CASE WHEN PrimaNota.Tipo = 0 THEN PrimaNota.dareavere ELSE 0 END) as Dare

but how can I add Sum function? If I add it before CASE WHEN it doesn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 07:55:21

sum(CASE WHEN PrimaNota.Tipo = 0 THEN PrimaNota.dareavere ELSE 0 END) as Dare

N 56°04'39.26"
E 12°55'05.63"

titan_ae78
Starting Member

9 Posts

Posted - 2009-07-01 : 08:01:43

right..

I've put bracket in wrong position.

Another question. I've this SQL statement:

SELECT
(CASE WHEN PrimaNota.Tipoconto=1 THEN Clienti_Fornitori_Agenti.Cliente ELSE PianoDeiConti.Descrizione END) As NomeConto,
PrimaNota.IDconto,

sum(CASE WHEN PrimaNota.Tipo = 0 THEN PrimaNota.dareavere ELSE 0 END) as Dare,
sum(CASE WHEN PrimaNota.Tipo = 1 THEN PrimaNota.dareavere ELSE 0 END) as Avere,
Dare - Avere as Saldo

FROM (PrimaNota left outer join PianoDeiConti ON PrimaNota.IDConto=PianoDeiConti.IDInfo_PianoDeiConti)
LEFT OUTER JOIN Clienti_Fornitori_Agenti ON PrimaNota.IDConto=Clienti_Fornitori_Agenti.IDInfo_Clienti_Fornitori_Agenti
GROUP BY PrimaNota.IDconto, PianoDeiConti.Descrizione, PrimaNota.Tipoconto,
(CASE WHEN PrimaNota.TipoConto = 1 THEN Clienti_Fornitori_Agenti.Cliente ELSE PianoDeiConti.Descrizione END)

but I've this error:
Messaggio 207, livello 16, stato 1, riga 6
column name 'Dare' is not valid.
Messaggio 207, livello 16, stato 1, riga 6
column name 'Avere' is not valid.

Can you help me?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 08:07:43

Use a derived table

SELECT		NomeConto,
		IDconto,
		SUM(Dare) AS Dare,
		SUM(Avere) AS Avere,
		SUM(Dare - Avere) as Saldo
FROM		(
			SELECT		CASE
						WHEN PrimaNota.Tipoconto = 1 THEN Clienti_Fornitori_Agenti.Cliente
						ELSE PianoDeiConti.Descrizione END
					END AS NomeConto,
					PrimaNota.IDconto,
					CASE
						WHEN PrimaNota.Tipo = 0 THEN PrimaNota.dareavere
						ELSE 0
					END AS Dare,
					CASE
						WHEN PrimaNota.Tipo = 1 THEN PrimaNota.dareavere
						ELSE 0
					END AS Avere
			FROM		PrimaNota
			LEFT JOIN	PianoDeiConti ON PianoDeiConti.IDInfo_PianoDeiConti = PrimaNota.IDConto
			LEFT JOIN	Clienti_Fornitori_Agenti ON Clienti_Fornitori_Agenti.IDInfo_Clienti_Fornitori_Agenti = PrimaNota.IDConto
		) AS d
GROUP BY	NomeConto,
		IDconto

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources