CODEWITHSUNDEEP
sqlsql-servergroup-bysum
Barrie van Boven
Barrie van Boven

Reputation: 199

Group by Total per week, and only show record with the maximum sum (Access)

Ok, so - IN SQL Server - I need to sum the count of records per week, and only show the week (and number of records in that week) that has the highest sum..

Up until now I can show each record, but I am only interested in the highest one. Any help would be appreciated.

*EDIT, since there is some confusion: I need a sum of occurances, grouped by week (so in this case: Project Q Unit 300 201435 2 is the highest, since the sum in week 38 for the same Project and unit combination is 1. ) The output should be as below.. What I get now is ALL Weeks with their respective sums, I only need to show the week with the Max sum

*hope that made sense :)

My table looks like this:

Project Name    Subunit name    Date of release     Origin      Week
Project X       SubUnit 1       28-Jul-14           Unit 100    201431
Project X       SubUnit 2       28-Jul-14           Unit 100    201431
Project X       SubUnit 3       28-Jul-14           Unit 100    201431
Project X       SubUnit 4       18-Sep-14           Unit 100    201438
Project X       SubUnit 5       18-Sep-14           Unit 100    201438
Project X       SubUnit 6       18-Sep-14           Unit 300    201438
Project X       SubUnit 7       05-Aug-14           Unit 300    201432
Project X       SubUnit 8       05-Aug-14           Unit 300    201432
Project X       SubUnit 9       18-Sep-14           Unit 600    201438
Project X       SubUnit 10      18-Sep-14           Unit 600    201438
Project X       SubUnit 11      18-Sep-14           Unit 600    201438
Project X       SubUnit 12      18-Sep-14           Unit 600    201438
Project Q       SubUnit 1       18-Sep-14           Unit 100    201438
Project Q       SubUnit 2       18-Sep-14           Unit 100    201438
Project Q       SubUnit 3       18-Sep-14           Unit 100    201438
Project Q       SubUnit 4       18-Sep-14           Unit 100    201438
Project Q       SubUnit 5       18-Sep-14           Unit 100    201438
Project Q       SubUnit 6       18-Sep-14           Unit 300    201438
Project Q       SubUnit 7       29-Aug-14           Unit 300    201435
Project Q       SubUnit 8       29-Aug-14           Unit 300    201435
Project Q       SubUnit 9       29-Aug-14           Unit 600    201435
Project Q       SubUnit 10      18-Sep-14           Unit 600    201438
Project Q       SubUnit 11      18-Sep-14           Unit 600    201438
Project Q       SubUnit 12      18-Sep-14           Unit 600    201438

Desired output is this:

Project Q   Unit 100    201438  5
Project Q   Unit 300    201435  2
Project Q   Unit 600    201438  3
Project X   Unit 100    201431  3
Project X   Unit 300    201432  2
Project X   Unit 600    201438  4

Upvotes: 2

Views: 612

Answers (1)

xQbert
xQbert

Reputation: 35343

Didn't know RDBMS so I assumed SQL SERVER and therefore that a CTE (Common Table Expression) would work.

We can avoid the cte by using subqueries if needed though... Also I eliminated spaces in column names as I didnt' know if I should use ` or [ or " to escape the field names....

http://sqlfiddle.com/#!3/82c01/14/0

With CTE AS (SELECT ProjectName, Origin, Week, count(projectname) as myCount
    FROM FOO
    GROUP BY ProjectName, Origin, week)
SELECT A.ProjectName, A.Origin, A.Week, A.MyCount
FROM CTE A
INNER JOIN (SELECT ProjectName, Origin, Max(myCount) MaxMyCount 
            FROM CTE
            GROUP BY ProjectName, Origin) B
   on A.Projectname = B.ProjectName
  and A.Origin = B.Origin
  and A.myCount = B.MaxMyCount
ORDER BY ProjectName, Origin, Week

Now as to what this does:

  • we generate a set of data (CTE) that contains the counts per project, origin, and week.
  • Next we use that set of data as a source and then join it to a subset of the CTE which only contains max counts per project name and origin.
  • since the join doesn't care about week, yet does care about count; we are able to join on the counts this inner join allows us to identify by count, projectname and origin, what weeks to return.
  • However this does assume a unique count per project name and origin. If not unique we will get duplicates in the results as MAX returns more than 1 record.

In essence I generated 3 sets of data.

  • 1 containing the aggregated counts by project, origin and week.
  • 1 containing just the the projects and origins and the max count for a project/origin
  • and then the result set which was the combination of the first 2.

RDBMS is SET based logic the more you think of things in terms of sets and subsets the easier (in my opinion) it is to build such queries.

Upvotes: 1

Related Questions