asked on

How can I add a field from another table when using a Group By clause?

I am using the following SQL statement to get the top 10 downloads of the month. However, I need to add _title.displayTitle to my results, but I can't add that field. It says "Column '_Title.displayTitle' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." How can I get displayTitle into my result set? Would I have to use a nested select statement?

SELECT TOP (5) _Download.titleidfk AS titleId, COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
 _Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2') AND ({ fn MONTH(_Download.dateTime) } = { fn MONTH(GETDATE()) })
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC

Open in new window

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ram4020

Add MAX. If there is only one title for each id.
SELECT TOP (5) _Download.titleidfk AS titleId, MAX (_title.displayTitle), COUNT(_Download.titleidfk) AS Downloads
FROM _Download INNER JOIN
_Title ON _Download.titleidfk = _Title.titleId
WHERE (_Title.typeId = '2') AND ({ fn MONTH(_Download.dateTime) } = { fn MONTH(GETDATE()) })
GROUP BY _Download.titleidfk
ORDER BY Downloads DESC

BYU-Studies

ASKER

Haha, I feel kind of retarded. I tried putting "_Title.displayTitle" at the beginning and at the end of the select clause, but I never tried it in the middle :). Thanks!

Bryan Butler

Just to confirm you have the field, if you do:
select count(_title.displayTitle) from _Title
Does that work?

If so, you should be able to do:

SELECT TOP (5) _Download.titleidfk AS titleId, _title.displayTitle, COUNT(_Download.titleidfk) AS Downloads
(etc..)