group by - 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)

group by

Author

Topic

tup
Starting Member

13 Posts

Posted - 2003-09-04 : 04:50:07

hi,

select p.partija ,a.naziv ,
sum(p.iznos),p.kod,p.dknizenja , p.tip

from pstdev p
join istdev i
on p.partija=i.partija and p.valuta = i.valuta
join adresar a on i.embg=a.embg
where
(p.dp=1 and p.status=0)
group by a.naziv

In the above query I'm trying to group p.iznos only by a.naziv , but I get this error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'p.PARTIJA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'p.KOD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'p.DKNIZENJA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'p.TIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is it posible in the situation like this not to include other fields in group by clause.

thanks to everyone.

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-04 : 05:26:18

Join the table to a subquery of itself where you select only the a.naziv ,
sum(p.iznos) grouped by a.naziv. Then in your main select you can relevant summed column.

-------
Moo. :)

tup
Starting Member

13 Posts

Posted - 2003-09-04 : 07:05:02

Sorry , but it seems that I'm not following you.
Can you be more concrete please?
thank you

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-09-04 : 07:46:57

[code]
SELECT B.partija ,B.naziv,
C_izons,B.kod,B.dknizenja,B.tip

FROM

(
select p.partija,a.naziv,
p.kod,p.dknizenja, p.tip
from pstdev p
join istdev i
on p.partija=i.partija and p.valuta = i.valuta
join adresar a on i.embg=a.embg
where
(p.dp=1 and p.status=0)
)
B

INNER JOIN

(
select a.naziv,sum(p.iznos) as C_izons,
from pstdev p
join istdev i
on p.partija=i.partija and p.valuta = i.valuta
join adresar a on i.embg=a.embg
where
(p.dp=1 and p.status=0)
group by a.naziv
)
C

ON B.naziv = C.naziv
[/code]

Voted best SQL forum nickname...."Tutorial-D"

tup
Starting Member

13 Posts

Posted - 2003-09-04 : 08:14:53

thank you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources