troubleshooting Question

Finding total files and files to download in Jobs

Avatar of Shawn
ShawnFlag for Canada asked on
Microsoft SQL ServerWeb ServersSQL
4 Comments1 Solution147 ViewsLast Modified:
I am having difficulty setting this query up to properly give 1) numer of files per Job and 2) the number of files that still need to be downloaded per folder.

The data in the select query before the grouping and counting is this:

JobReference  MemberFileID  MemberFileDownloadID
TR09F72      113      40
TR09F72      113      41
TR09F72      113      42
TR09F72      113      43
TR09F72      113      37
TR09F72      116      NULL
TR09F75      115      39
TR09F75      115      38

The result I woant should look like this:
JobReference  FileCount  FilesToDownload
TR09F72      2      1
TR09F75      1      0

below is the select query, then an attempt to get the results.

How can I do this query?
select query:
SELECT     tblMemberJobs.JobReference, tblMemberFiles.MemberFileID, tblMemberFileDownloads.MemberFileDownloadID
FROM         tblMemberFileDownloads RIGHT OUTER JOIN
                      tblMemberFiles INNER JOIN
                      tblMemberFileBatch ON tblMemberFiles.MemberFileBatchID = tblMemberFileBatch.MemberFileBatchID INNER JOIN
                      tblMemberJobs ON tblMemberFileBatch.FileBatchReference = tblMemberJobs.JobReference ON 
                      tblMemberFileDownloads.MemberFileID = tblMemberFiles.MemberFileID
 
attempt at grouping query
SELECT     tblMemberJobs.JobReference, COUNT(tblMemberFiles.MemberFileID) AS FileCount, COUNT(tblMemberFileDownloads.MemberFileDownloadID) 
                      AS FilesToDownload
FROM         tblMemberFileDownloads RIGHT OUTER JOIN
                      tblMemberFiles INNER JOIN
                      tblMemberFileBatch ON tblMemberFiles.MemberFileBatchID = tblMemberFileBatch.MemberFileBatchID INNER JOIN
                      tblMemberJobs ON tblMemberFileBatch.FileBatchReference = tblMemberJobs.JobReference ON 
                      tblMemberFileDownloads.MemberFileID = tblMemberFiles.MemberFileID
GROUP BY tblMemberJobs.JobReference
 
results:
JobReference	FileCount	FilesToDownload
TR09F72	6	5
TR09F75	2	2
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros