CODEWITHSUNDEEP
mysql
Anthony Weru
Anthony Weru

Reputation: 35

mySQL groupwise minimum and maximum

I am relatively new to mySQL and I'm fooling around with the groupwise maximum example from https://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html.

My goal is to get the groupwise maximum and groupwise minimum for each article. Here is the schema I'm using:

CREATE TABLE shop (`article` int(10), `dealer` varchar(10), `price` int(10));

INSERT INTO shop 
    (`article`, `dealer`, `price`)
VALUES
    (26, 'PCS', 120),
    (27, 'PCS', 345),
    (28, 'RDN', 211),
    (29, 'RDN', 99),
    (26, 'PCS', 111);

The query I have at the moment:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article) OR price=(SELECT MIN(s3.price)
              FROM shop s3
              WHERE s1.article = s3.article)
ORDER BY article;

The result of the above query:

article dealer price
26 PCS 120
26 PCS 111
27 PCS 345
28 RDN 211
29 RDN 99

I think the query works as it should but I'm trying to achieve the following output:

article dealer max_price min_price
26 PCS 120 111
27 PCS 345 345
28 RDN 211 211
29 RDN 99 99

Link to SQL Fiddle

How do I go about this? Thanks.

Upvotes: 0

Views: 192

Answers (1)

Syed
Syed

Reputation: 146

subqueries are not required. This query will get you min and max by article and dealer

SELECT article, dealer, max(price) as max_price, min(price) as min_price
FROM   shop s1
group by 1,2
order by 1,2

in case dealers are different for the same article you can use this

SELECT article, max(price) as max_price, min(price) as min_price
    FROM   shop s1
    group by 1
    order by 1

Upvotes: 1

Related Questions