count over a query - 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

count over a query

Author

Topic

ymamalis
Starting Member

42 Posts

Posted - 2009-08-07 : 01:22:09

hi . i have this query
. i want to group the results in way that it should show me how many
lhpths_asfalishs.id_ontothta have proionta.
now it shows me how many products("proionta") has each client (lhpths_asfalishs.id_ontothta ). i want to have how many clients have 5 products , how many 4 products , how many 1 products e.t.c.

SELECT COUNT(ypoklados_asfalishs.id_kladoy) AS Proionta, SYMB_lhpths_asfalishs.id_ontothta
FROM ypoklados_ana_asfal_etaireia INNER JOIN
SYMB_symbolaia ON ypoklados_ana_asfal_etaireia.id_Y_A_A_E = SYMB_symbolaia.id_ypoklados_asf_et INNER JOIN
ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy INNER JOIN
SYMB_lhpths_asfalishs ON SYMB_symbolaia.id_symbolaioy = SYMB_lhpths_asfalishs.id_symbolaioy
WHERE (dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 4 AND dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 5)
GROUP BY SYMB_lhpths_asfalishs.id_ontothta

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-07 : 01:47:47

[code]
SELECT Proionta, COUNT(SYMB_lhpths_asfalishs.id_ontothta)
FROM
(
SELECT COUNT(ypoklados_asfalishs.id_kladoy) AS Proionta,
SYMB_lhpths_asfalishs.id_ontothta
FROM ypoklados_ana_asfal_etaireia
INNER JOIN SYMB_symbolaia ON ypoklados_ana_asfal_etaireia.id_Y_A_A_E = SYMB_symbolaia.id_ypoklados_asf_et
INNER JOIN ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
INNER JOIN SYMB_lhpths_asfalishs ON SYMB_symbolaia.id_symbolaioy = SYMB_lhpths_asfalishs.id_symbolaioy
WHERE dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 4
AND dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 5
GROUP BY SYMB_lhpths_asfalishs.id_ontothta
) d
[/code]

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

ymamalis
Starting Member

42 Posts

Posted - 2009-08-07 : 01:57:45

that query produces an error ("the multi part.......SYMB_lhpths_asfalishs.id_ontothta could not be found"

Sachin.Nand

2937 Posts

Posted - 2009-08-07 : 02:00:00

quote:
Originally posted by ymamalis

that query produces an error ("the multi part.......SYMB_lhpths_asfalishs.id_ontothta could not be found"

SELECT Proionta, COUNT(d.id_ontothta)
FROM
(
SELECT COUNT(ypoklados_asfalishs.id_kladoy) AS Proionta,
SYMB_lhpths_asfalishs.id_ontothta
FROM ypoklados_ana_asfal_etaireia
INNER JOIN SYMB_symbolaia ON ypoklados_ana_asfal_etaireia.id_Y_A_A_E = SYMB_symbolaia.id_ypoklados_asf_et
INNER JOIN ypoklados_asfalishs ON ypoklados_ana_asfal_etaireia.id_ypokladoy = ypoklados_asfalishs.id_ypokladoy
INNER JOIN SYMB_lhpths_asfalishs ON SYMB_symbolaia.id_symbolaioy = SYMB_lhpths_asfalishs.id_symbolaioy
WHERE dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 4
AND dbo.getStatusIdByidSymb(SYMB_lhpths_asfalishs.id_symbolaioy) <> 5
GROUP BY SYMB_lhpths_asfalishs.id_ontothta
) d

PBUH

ymamalis
Starting Member

42 Posts

Posted - 2009-08-07 : 02:02:14

column d.proionta is invalid in the select list because it is not contained ineither an aggregate function...... error

Sachin.Nand

2937 Posts

Posted - 2009-08-07 : 02:10:35