CODEWITHSUNDEEP
ms-accesssubqueryms-access-2013
RedRaven
RedRaven

Reputation: 735

In MS Access how to get the largest row in each group

I am trying to get the row with the largest number col5 based on a sum as below:

SELECT
    Col1,
    Col2,
    col3,
    col4,
    SUM(col5) AS TOTAL_col5
FROM table1
WHERE (((Col1)=variable1) AND ((col3)="variable2"))
GROUP BY    
    Col1,
    Col2,
    col3,
    col4
ORDER BY
    col4,
    SUM(col5) AS TOTAL_col5 DESC

I have tried using Top 1 in the select though this only brings back one row, I have also tried doing a MAX(SUM(col5) AS TOTAL_col5) but I get errors because it seems you can't combine aggregates. I think the answer is a subquery, but I can't wrap my head around how to write it.

More detail as per HarveyFrench: I am looking for the outer query that would then return a table which has the LARGEST sum in col5 for every combination of cols 1 to 4. (The inner query returns ALL of the sums for each combination). Based on his input I tried the following, but it still returns everyting

SELECT  
        T1.Col1, 
        T1.Col2, 
        T1.Col3, 
        T1.Col4,
        MAX(T1.TOTAL_col5)
FROM
    (   
    SELECT
    Col1,
    Col2,
    col3,
    col4,
    SUM(col5) AS TOTAL_col5
FROM table1
WHERE (((Col1)=variable1) AND ((col3)="variable2"))
GROUP BY    
    Col1,
    Col2,
    col3,
    col4
ORDER BY
    col4,
    SUM(col5) AS TOTAL_col5 DESC

    ) AS T1
GROUP BY    
        T1.Col1, 
        T1.Col2, 
        T1.Col3, 
        T1.Col14

Upvotes: 0

Views: 41

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4578

I don't quite understand what you are asking "how to get the largest row in each group".

In the SQL below, the inner query returns a table T1 which sums col5 for every combination of cols 1 to 4.

The outer query find the highest SUMof col5 for each col4.

I think this is what you are asking for, but until you question is clearer, I can't help any further.

SELECT col4
     , MAX(TOTAL_col5)
FROM
(
    SELECT
        Col1,
        Col2,
        col3,
        col4,
        SUM(col5) AS TOTAL_col5
    FROM table1
    WHERE (((Col1)=variable1) AND ((col3)="variable2"))
    GROUP BY    
        Col1,
        Col2,
        col3,
        col4
) AS T1
GROUP BY col4
)

Upvotes: 1

Related Questions