CODEWITHSUNDEEP
mysqlsql
Veign
Veign

Reputation: 606

Min and Max on a SUM column

I have a table like:

Phrase    |  qty
phrase_1  |  4  
phrase_1  |  1
phrase_1  |  8
phrase_2  |  2
phrase_3  |  3
phrase_3  |  2

What I initially return is:

phrase_1 | 13
phrase_3 | 5
phrase_2 | 2

Using:

SELECT phrase, sum(qty) as total
FROM mytable
GROUP By phrase
ORDER BY total DESC

What I need, and can't figure out, is how to return the min and max with the results.

so I would get:

phrase, qty, min, max
phrase_1 | 13 | 2 | 13
phrase_3 | 5  | 2 | 13
phrase_2 | 2  | 2 | 13

Because I want to run a normalization on the resultset and return a new order based on values between 1 and 0.

Something like (this doesn't work):

SELECT phrase, sum(qty) as total, (total - min(total)/max(total) - min(total)) AS rank
FROM mytable
GROUP By phrase
ORDER BY rank DESC

The above statement is ultimiately what I'm looking to do and not sure if it's possible.

Upvotes: 0

Views: 74

Answers (3)

Rostam Bamasi
Rostam Bamasi

Reputation: 274

You can use code below :

SELECT phrase, sum(qty) as total,
       MIN(SUM(qty)) OVER () as min_total,
       MAX(SUM(qty)) OVER () as max_total
into #temp
FROM mytable
GROUP By phrase
ORDER BY total DESC

Select *,(total - min_total/max_total - min_total) AS rank From #temp
Drop Table #temp

Upvotes: 0

nbk
nbk

Reputation: 49373

With some subqueries you can achieve your goal, but pretty it will never get

CREATE TABLE mytable (
  `Phrase` VARCHAR(8),
  `qty` INTEGER
);

INSERT INTO mytable
  (`Phrase`, `qty`)
VALUES
  ('phrase_1', '4'),
  ('phrase_1', '1'),
  ('phrase_1', '8'),
  ('phrase_2', '2'),
  ('phrase_3', '3'),
  ('phrase_3', '2');

SELECT phrase,total,(total - mi/ma - mi) AS rank
FROM
 (SELECT phrase, sum(qty) as total 
FROM mytable
GROUP By phrase
ORDER BY total DESC) t1 CROSS JOIN (SELECT MIN(total) mi,MAX(total) ma
FROM
 (SELECT phrase, sum(qty) as total 
FROM mytable
GROUP By phrase
ORDER BY total DESC) t1) t2

phrase   | total |    rank
:------- | ----: | ------:
phrase_1 |    13 | 10.8462
phrase_3 |     5 |  2.8462
phrase_2 |     2 | -0.1538

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want window functions:

SELECT phrase, sum(qty) as total,
       MIN(SUM(qty)) OVER () as min_total,
       MAX(SUM(qty)) OVER () as max_total
FROM mytable
GROUP By phrase
ORDER BY total DESC

Upvotes: 1

Related Questions