Select max of sum and max of count of a subquery

Question

I have a query which gives me the sum and counts per category. It is sorted descending. The first value is the max value, so the highest sum and highest count.

SELECT SUM(score) AS calcsum, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY SUM(score) DESC;

resp.

SELECT count(*) as counted, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY count(*) DESC;

However, now I do not want a list, I just want the maximium value. I want to implement it first with max of count and max of sum. And second way is to just extract the first observation of the list.

First method I tried:

SELECT category, MAX(calcsum)
FROM (
SELECT SUM(score) AS calcsum, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY SUM(score) DESC);

resp.

SELECT category, MAX(counted)
FROM (
SELECT count(*) AS counted, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY count(*) DESC);

Which leads to an error message "An action query cannot be used as a row source".

Second way I tried:

SELECT TOP 1 * 
 FROM (
    SELECT SUM(score) AS calcsum, category
    INTO newdataset
    FROM table1 INNER JOIN
         table2
         ON table1.keys  =table2.ID
    WHERE table2.filter = "Value"
    GROUP BY category
    ORDER BY SUM(score) DESC);

resp.

SELECT TOP 1 * 
 FROM (
    SELECT SUM(score) AS calcsum, category
    INTO newdataset
    FROM table1 INNER JOIN
         table2
         ON table1.keys  =table2.ID
    WHERE table2.filter = "Value"
    GROUP BY category
    ORDER BY SUM(score) DESC);

Same error here. What is the problem here, why is it not working and I do not understand the error message? I would like to have working solutios for both ways, how would a working solution look like?

Update: I tried according to the answers:

SELECT category, max(calcsum)
INTO newdataset
FROM (SELECT SUM(score) AS calcsum, category      
      FROM table1 INNER JOIN
           table2
           ON table1.keys = table2.ID
      WHERE table2.filter = "Value"
      GROUP BY category
     ) t;

However, I get an error message (category is not part of the aggregate function, translated)?

I also tried:

 SELECT t.category, max(calcsum)
    INTO newdataset
    FROM (SELECT SUM(score) AS calcsum, category      
          FROM table1 INNER JOIN
               table2
               ON table1.keys = table2.ID
          WHERE table2.filter = "Value"
          GROUP BY category
         ) t;

But same error. So how would a working solution using the max way and not top look like?

When I try this:

 SELECT category, calcsum
    INTO newdataset
    FROM (SELECT SUM(score) AS calcsum, category      
          FROM table1 INNER JOIN
               table2
               ON table1.keys = table2.ID
          WHERE table2.filter = "Value"
          GROUP BY category
         ) t;

It works, but when I add max(calcsum), I get the error (category is not part of the aggregate function, translated)?

Answer 1

Is there any specific purpose to use sub-query format as this queries can also return output without using sub-queries. Any way you have sorted the "calcsum" in descending order. Limit should do the job for you.

SELECT SUM(score) AS calcsum, category
FROM table1 INNER JOIN
 table2
 ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY calcsum DESC LIMIT 1

Answer 2

Frankly, I do not understand the reason why you require to not using top/order-by as suggested by GMB.

Anyway, here's the query using MAX() function. Please be aware, this is a costly query.

SELECT SUM(score) AS calcsum, category 
INTO newdataset
FROM table1 
INNER JOIN table2 ON table1.keys = table2.id 
WHERE table2.filter = "Value" 
GROUP BY category 
HAVING SUM(score) = (
    SELECT MAX(sc) 
    FROM (
        SELECT SUM(score) AS sc
        FROM table1 
        INNER JOIN table2 ON table1.keys = table2.id 
        WHERE table2.filter = "Value" 
        GROUP BY category
      )
  )

And the same principle applied to counted:

SELECT COUNT(*) AS counted, category 
INTO newdataset
FROM table1 
INNER JOIN table2 ON table1.keys = table2.id 
WHERE table2.filter = "Value" 
GROUP BY category 
HAVING COUNT(*) = (
    SELECT MAX(sc) 
    FROM (
        SELECT COUNT(*) AS sc
        FROM table1 
        INNER JOIN table2 ON table1.keys = table2.id 
        WHERE table2.filter = "Value" 
        GROUP BY category
      )
  )

Answer 3

The reason your versions do not work is because INTO is only allowed in the outermost query. You are trying to put it in a subquery. In addition:

• ORDER BY is generally not allowed in a subquery.
• Subqueries in the FROM clause need a table alias.

So, the errors multiple.

For instance, you could write the last query as:

SELECT TOP (1) t.* 
INTO newdataset
FROM (SELECT SUM(score) AS calcsum, category      
      FROM table1 INNER JOIN
           table2
           ON table1.keys = table2.ID
      WHERE table2.filter = "Value"
      GROUP BY category
     ) t
ORDER BY SUM(score) DESC;

As GMB points out, you don't need a subquery for this logic.

I would point out that if you have duplicates for the maximum values, then the logic chooses an arbitrary maximum. If you want all of them, use TOP (1) WITH TIES.

I would go one step further and question why you need this in a new table. Why not just store the values in parameters if you want to use the in subsequent processing?

Answer 4

If you want the just the top record in the resultset, why not simply limit the returned rows in the outer query?

SELECT TOP (1) SUM(score) AS calcsum, category
INTO newdataset
FROM table1 
INNER JOIN table2 ON table1.keys = table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY SUM(score) DESC;