CODEWITHSUNDEEP
mysqlsqlsummax
user3052604
user3052604

Reputation: 136

MAX() and SUM() in one query

I have this table

dept | amount | price
   1 |      2 | 20
   3 |      2 | 50
   4 |      3 | 10
   2 |      5 | 20
   1 |      1 | 15
   4 |      1 | 30
   4 |      6 |  5
   2 |      7 |  7
   1 |      1 | 24
   2 |      5 | 12

dept is de department number amount is how many of a product is sold. price is how much the price of the product is

How can I found the dept, that has got the most money from selling their products.

I have this:

SELECT dept, SUM( amount * price ) AS total
FROM table
GROUP BY dept

I need it to return the dept with the highest total. I can't use MAX( SUM( amount * price ) ), so how do I do this?

Oh yeah. It's a school assignment and I may not use LIMIT or ORDER BY

Upvotes: 0

Views: 2344

Answers (4)

LHA
LHA

Reputation: 9655

If you do not want to use ORDER and LIMIT. This is a solution ( Tested)

SELECT dept, SUM( amount * price ) AS total
FROM table
GROUP BY dept
HAVING SUM( amount * price ) = ( SELECT MAX(A.total)
                                 FROM
                                 (
                                     SELECT dept, SUM( amount * price ) AS total
                                     FROM table
                                     GROUP BY dept
                                  ) A
                                )

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

Without using LIMIT you can try using HAVING:

SELECT dept,SUM(amount * price) AS total
FROM tab1
GROUP BY dept
HAVING SUM(amount * price) = (
    SELECT MAX(total)
    FROM (
      SELECT SUM(amount * price) AS total
      FROM tab1
      GROUP BY dept
      ) a
    )

sqlfiddle demo

Upvotes: 4

juergen d
juergen d

Reputation: 204854

You can sort by the total descending and take the first entry

SELECT dept, SUM( amount * price ) AS total 
FROM table 
GROUP BY dept
order by total desc
limit 1

Upvotes: 0

Stefan
Stefan

Reputation: 1739

This will give you the department with the highest total:

select top 1 dept, sum(amount * price)
from table
group by dept
order by sum(amount * price) desc

Upvotes: 0

Related Questions