How easy thing become difficult...

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 2005 Forums

Transact-SQL (2005)

How easy thing become difficult...

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-07-03 : 05:47:00

I did:

select
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
pv.Provincienaam as Provincie,
i.InstellingNaam as InstellingNaam
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
inner join ZMProvincie pv on (lo.LocatiehoofdprovincieID = pv.ProvincieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)
where
pr.ProductPublicatieStatus = 1
and
st.InstellingSoortID = 1

order by
i.InstellingNaam
DESC

And then changed it to:

select
count(*) as Count,
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
pv.Provincienaam as Provincie,
i.InstellingNaam as InstellingNaam
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
inner join ZMProvincie pv on (lo.LocatiehoofdprovincieID = pv.ProvincieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)
where
pr.ProductPublicatieStatus = 1
and
st.InstellingSoortID = 1

order by
i.InstellingNaam
DESC

And then I get a:

Msg 8120, Level 16, State 1, Line 1
Column 'ZMProduct.ProductID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is probably easy for you, but sounds like chinese to me...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 05:56:06

to do an aggregation you have to group on the columns you want the aggregation to work on.

in your case you have to add all of your columns that appear int the select list in the group by clause.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-07-03 : 05:58:31

What if I just want to return the records and the number of records.
Is there some simple way...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 06:14:48

select @@rowcount

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources