Order by Foreign Key Count - 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)

Order by Foreign Key Count

Author

Topic

twista
Starting Member

1 Post

Posted - 2006-11-21 : 12:24:46

Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.

I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.

catalog
-----------
id - PK
act_name
(price and other such info)

Photos
-----------
id - PK
path_to_photo
event_id - FK(catalog.id)

What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.

here is the query that I'm using right now.

SELECT	COUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name, 
		catalog.location, catalog.price_adult,	
		catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed, 
		catalog.long_description, catalog.online,
		catalog.act_type, event_types.act_type AS evt_type,
		event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets,
		photos.id
		FROM catalog, event_types, photos
WHERE catalog.act_type =  event_types.id 
          AND photos.event_id = catalog.id 
          GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name 
          ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name

Which returns the following error.
Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.

Anyone got an idea what I'm doing wrong?

Thanks in advance,
Morgan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 12:31:23

[code]SELECT COUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name,
catalog.location, catalog.price_adult,
catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed,
catalog.long_description, catalog.online,
catalog.act_type, event_types.act_type AS evt_type,
event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets,
photos.id
FROM catalog
inner join event_types on event_types.id = catalog.act_type
inner join photos on photos.event_id = catalog.id
GROUP BY ~~COUNT(photos.id),~~ catalog.id, catalog.company, catalog.act_name
ORDER BY ~~photos.id,~~ catalog.id, catalog.company, catalog.act_name[/code]

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 14:51:09

Which 3 forums have you been through? Just curious.

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources