sum of a field - 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

General SQL Server Forums

New to SQL Server Programming

sum of a field

Author

Topic

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 11:12:13

hello everybody. i am trying to get a sum of the fiel 'kathara' applying the condition
hmer_ekdoshs <= DATEADD(y, 1, HMER_ENARJHS_SYMB))

SELECT HMER_ENARJHS_SYMB, id_symbolaioy, kostos_kath AS Kathara, hmer_ekdoshs AS ekdosh
FROM APOD_apodeijeis
WHERE (id_symbolaioy = @idSymb)
ORDER BY id_symbolaioy

can you help me how to achive that ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 11:23:50

[code]
SELECT SUM(Kathara)
FROM
(
SELECT HMER_ENARJHS_SYMB, id_symbolaioy, kostos_kath AS Kathara, hmer_ekdoshs AS ekdosh
FROM APOD_apodeijeis
WHERE (id_symbolaioy = @idSymb)
) d
WHERE ekdosh <= DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 11:28:29

thanks a lot my friend !!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 11:29:20

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 11:44:17

can i ask for a bit more complex help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 11:44:41

sure

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 11:49:14

i developed the query you send my with this one

SELECT SUM(kathara) AS AethAsfalistra
FROM (SELECT APOD_apodeijeis.id_ontotitas_paragogos, APOD_apodeijeis.HMER_ENARJHS_SYMB, APOD_apodeijeis.hmer_enarjhs AS enarjh,
APOD_apodeijeis.kostos_kath AS kathara, ypoklados_asfalishs.perigrafh_ypokladoy
FROM APOD_apodeijeis INNER JOIN
ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E INNER JOIN
ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)) AS derivedtbl_1
WHERE (enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) AND (YEAR(HMER_ENARJHS_SYMB) = @year)

and i need 2 functionalities

first i want to group me the result by 'ypoklados_asfalishs.perigrafh_ypokladoy'

and the 2nd i want to delete the criteria 'WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg))' and to present me a pivot table grouped by id_ontotitas_paragogos and columns the 'ypoklados_asfalishs.perigrafh_ypokladoy'

is it possible for you to help me please ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 11:53:35

if you are using SQL 2005/2008 you can use the PIVOT operator.

Are you using SQL 2000 or 2005/2008 ?

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 11:57:36

yes i am using 2005 i found this article http://msdn.microsoft.com/en-us/library/ms177410.aspx but i understand nothing !!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 12:01:04

1st the GROUP BY


SELECT ypoklados_asfalishs.perigrafh_ypokladoy, SUM(kathara) AS AethAsfalistra
FROM 
(
	SELECT 	APOD_apodeijeis.id_ontotitas_paragogos, 
		APOD_apodeijeis.HMER_ENARJHS_SYMB, 
		APOD_apodeijeis.hmer_enarjhs AS enarjh,
		APOD_apodeijeis.kostos_kath AS kathara, 
		ypoklados_asfalishs.perigrafh_ypokladoy
	FROM 	APOD_apodeijeis 
	INNER JOIN ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E 
	INNER JOIN ypoklados_asfalishs 		ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
	WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)
) AS derivedtbl_1
WHERE (enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) AND (YEAR(HMER_ENARJHS_SYMB) = @year)
GROUP BY ypoklados_asfalishs.perigrafh_ypokladoy

is this what you want ?

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 12:04:04

yes i changed to
SELECT ypo, SUM(kathara) AS AethAsfalistra
FROM (SELECT APOD_apodeijeis.id_ontotitas_paragogos, APOD_apodeijeis.HMER_ENARJHS_SYMB, APOD_apodeijeis.hmer_enarjhs AS enarjh,
APOD_apodeijeis.kostos_kath AS kathara, ypoklados_asfalishs.perigrafh_ypokladoy AS ypo
FROM APOD_apodeijeis INNER JOIN
ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E INNER JOIN
ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)) AS derivedtbl_1
WHERE (enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) AND (YEAR(HMER_ENARJHS_SYMB) = @year)
GROUP BY ypo

and it is ok !!! thanks fro the 1st !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 12:04:34

next the PIVOT


SELECT	perigrafh_ypokladoy, [value1], [value2], [value3]
FROM
(
	SELECT 	APOD_apodeijeis.id_ontotitas_paragogos, 
		ypoklados_asfalishs.perigrafh_ypokladoy, 
		SUM(kathara) AS AethAsfalistra
	FROM 
	(
		SELECT 	APOD_apodeijeis.id_ontotitas_paragogos, 
			APOD_apodeijeis.HMER_ENARJHS_SYMB, 
			APOD_apodeijeis.hmer_enarjhs AS enarjh,
			APOD_apodeijeis.kostos_kath AS kathara, 
			ypoklados_asfalishs.perigrafh_ypokladoy
		FROM 	APOD_apodeijeis 
		INNER JOIN ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E 
		INNER JOIN ypoklados_asfalishs 		ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
	-- 	WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)
	) AS derivedtbl_1
	WHERE (enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) AND (YEAR(HMER_ENARJHS_SYMB) = @year)
	GROUP BY APOD_apodeijeis.id_ontotitas_paragogos,
		ypoklados_asfalishs.perigrafh_ypokladoy
) d
PIVOT	
(
	SUM (AethAsfalistra)
	FOR id_ontotitas_paragogos IN ([value1], [value2], [value3]) -- put the value of id_ontotitas_paragogos in [ ] that you want to pivot
) p

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 12:11:51

sorry i want to display for all the id_ontotitas_paragogos that found in the table i mean the result should be somthing like
id_ontotitas_paragogos ypo 1 , ypo2 , ypo 3, ypo 4 , ypo 5 , ypo 6
1 1000 , 0 , 20 , 0 , 0 , 0
2 0, , 100 , 23 , 2000 , 1000 , 3000
et.c.

ymamalis
Starting Member

42 Posts

Posted - 2009-07-19 : 12:43:43

i can not make it play !!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 21:26:17

show us your query

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-21 : 15:29:17

SELECT perigrafh_ypokladoy
FROM
(
SELECT APOD_apodeijeis.id_ontotitas_paragogos,
ypoklados_asfalishs.perigrafh_ypokladoy,
SUM(kathara) AS AethAsfalistra
FROM
(
SELECT APOD_apodeijeis.id_ontotitas_paragogos,
APOD_apodeijeis.HMER_ENARJHS_SYMB,
APOD_apodeijeis.hmer_enarjhs AS enarjh,
APOD_apodeijeis.kostos_kath AS kathara,
ypoklados_asfalishs.perigrafh_ypokladoy
FROM APOD_apodeijeis
INNER JOIN ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E
INNER JOIN ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
-- WHERE (APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)
) AS derivedtbl_1
WHERE (enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) AND (YEAR(HMER_ENARJHS_SYMB) = @year)
GROUP BY APOD_apodeijeis.id_ontotitas_paragogos,
ypoklados_asfalishs.perigrafh_ypokladoy
) d
PIVOT
(
SUM (AethAsfalistra)

) p

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 19:37:24

you need to list down what are the possible value for id_ontotitas_paragogos (highlighted in red) that you are pivoting


SELECT 	perigrafh_ypokladoy, [val1], [val2], [val3], [val4]
FROM
(
	SELECT 	APOD_apodeijeis.id_ontotitas_paragogos,
		ypoklados_asfalishs.perigrafh_ypokladoy,
		SUM(kathara) AS AethAsfalistra
	FROM
	(
		SELECT 	APOD_apodeijeis.id_ontotitas_paragogos,
			APOD_apodeijeis.HMER_ENARJHS_SYMB,
			APOD_apodeijeis.hmer_enarjhs AS enarjh,
			APOD_apodeijeis.kostos_kath AS kathara,
			ypoklados_asfalishs.perigrafh_ypokladoy
		FROM 	APOD_apodeijeis
			INNER JOIN ypoklados_ana_asfal_etaireia ON APOD_apodeijeis.id_ypoklados_asf_et = ypoklados_ana_asfal_etaireia.id_Y_A_A_E
			INNER JOIN ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
-- 		WHERE 	(APOD_apodeijeis.id_ontotitas_paragogos = @id_synerg)
	) AS derivedtbl_1
	WHERE 	(enarjh < DATEADD(YEAR, 1, HMER_ENARJHS_SYMB)) 
	AND 	(YEAR(HMER_ENARJHS_SYMB) = @YEAR)
	GROUP BY APOD_apodeijeis.id_ontotitas_paragogos,
		ypoklados_asfalishs.perigrafh_ypokladoy
) d
PIVOT
(
	SUM (AethAsfalistra)
	FOR id_ontotitas_paragogos IN ([val1], [val2], [val3], [val4])
) p

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2009-07-24 : 00:50:04

And what if I want the pivot for all the Id ontotitas paragogos in the tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources