Thanks for your help!
I'd like to output all companyName entries that have uploads across any of their serverFiles as:
companies.companyName – count(files.fileID) – sum(serverFiles.uniqueUploads)
Initech Ltd. - 11 - 24931
Epiphyte Inc. - 23 - 938821
Here are the relavent parts of my table structure:
Table: companies
companyID (INT) - companyName (TEXT)
Table: campaigns
campaignID(INT) - companyID (INT)
Table: files
fileID (INT) - campaignID (INT)
Table: serverFiles
serverFileID (INT) – fileID (INT) – uniqueUploads(INT)
Each company can have multiple campaigns.
Each campaign can have multiple files.
Each file can have multiple serverFiles
The query I tried to use is as follows:
SELECT companies.companyName, sum(serverFiles.uniqueUploads), count(files.fileID)
FROM companies
INNER JOIN campaigns on companies.companyID = campaigns.companyID
INNER JOIN files on files.campaign = campaigns.campaignID
LEFT OUTER JOIN serverFiles on serverfiles.fileID = files.fileID
GROUP BY serverFiles.uniqueUploads, files.fileID
ORDER BY sum(serverFiles.uniqueUploads) DESC;
However, this returns multiple entries for each company (as it creates multiple outputs for each company – one for each campaign under the company).
eg:
companies.companyName – count(files.fileID) – sum(serverFiles.uniqueUploads)
Initech Ltd. - 2 - 234234 (for initech campaign1)
Initech Ltd. - 4 - 223323 (for initech campaign2)
Epiphyte Inc. - 13 - 6434 (for epiphyte campaign1)
Initech Ltd. - 1 - 554 (for initech campaign3)
Epiphyte Inc. - 13 - 7544 (for epiphyte campaign2)
Epiphyte Inc. - 11 - 74545 (for epiphyte campaign3)
Epiphyte Inc. - 23 - 456544 (for epiphyte campaign4)
How do I change the query to only give me one response back for each company, summing up all of the campaigns under that company.
Thanks so much for any assistance.
Best Answer
Your query is fine, I think, except for the GROUP BY clause. Should be