• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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

0
BYU-Studies
Asked:
BYU-Studies
1 Solution
 
YZlatCommented:
just add it to GROUP BY
SELECT TOP (5) _Download.titleidfk AS titleId,_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, _Title.displayTitle
ORDER BY Downloads DESC

Open in new window

0
 
Ram4020Commented:
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
0
 
BYU-StudiesAuthor Commented:
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!
0
 
Bryan ButlerCommented:
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..)



0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now