I have a table that contains a set of products purchased and their total quantities. How to get the product having minimum quantity for that month?

I want to extract the products corresponding to each month having minimum and the maximum quantity. That is for month 1 the product having the least quantity that is “Butter” should be displayed along with the quantity and the product having the maximum quantity that is “Eggs” should be displayed.

Month   Product  Quantity

1      Bread     10177
1      Butter    5009
1      Coke      14381
1      Cookies   6892
1      Eggs      14783

2      Bread     10819
2      Butter    16156
2      Coke      23908
2      Cookies   9260
2      Eggs      10005

3      Bread     10933
3      Butter    4534
3      Cookies   488
3      Eggs      1744

4      Bread     12671
4      Butter    8982
4      Coke      9707
4      Cookies   3669
4      Eggs      19922

4      Yogurt    8063
5      Bread     14759
5      Coke      13346
5      Cookies   3139
5      Eggs      10137
5      Fruits    10961

6      Bread     14628
6      Butter    12447
6      Coke      9282
6      Cookies   19783
6      Eggs      6017

7      Bread     2573
7      Butter    20273
7      Coke      16162
7      Cookies   20156
7      Eggs      7678

8      Bread     7857
8      Butter    2529
8      Coke      10523
8      Cookies   3494
8      Eggs      4754


9      Bread     10382
9      Butter    9414
9      Coke      2714
9      Cookies   8602
9      Eggs      10772

10     Bread     4309
10     Butter    9253
10     Coke      17872
10     Cookies   14082
10      Eggs     14003
10     Fruits    9419

11     Bread     5308
11     Butter    6488
11     Coke      1998
11     Cookies   15450

12     Bread     7291
12     Butter    5968
12     Coke      8904
12     Cookies   12579
12     Eggs      4246

Answer

You can try to use MAX and MIN by month in subquery then self join

SELECT t2.*
FROM (
    SELECT Month,
           MAX(Quantity) maxQ,
           MIN(Quantity) minQ
    FROM T
    GROUP BY Month   
) t1 JOIN T t2 on t1.Month = t2.Month and (t2.Quantity = maxQ or t2.Quantity =minQ)

another way you can use exists subquery.

Query 1:

SELECT t2.*
FROM T t2
WHERE exists(
    SELECT 1
    FROM T t1
    WHERE t1.Month = t2.Month
    GROUP BY Month
    HAVING  
        MAX(Quantity) = t2.Quantity
    OR 
        MIN(Quantity) = t2.Quantity
)

Results:

| month | product | quantity |
|-------|---------|----------|
|     1 |  Butter |     5009 |
|     1 |    Eggs |    14783 |
|     2 |    Coke |    23908 |
|     2 | Cookies |     9260 |
|     3 |   Bread |    10933 |
|     3 | Cookies |      488 |
|     4 | Cookies |     3669 |
|     4 |    Eggs |    19922 |
|     5 |   Bread |    14759 |
|     5 | Cookies |     3139 |
|     6 | Cookies |    19783 |
|     6 |    Eggs |     6017 |
|     7 |   Bread |     2573 |
|     7 |  Butter |    20273 |
|     8 |  Butter |     2529 |
|     8 |    Coke |    10523 |
|     9 |    Coke |     2714 |
|     9 |    Eggs |    10772 |
|    10 |   Bread |     4309 |
|    10 |    Coke |    17872 |
|    11 |    Coke |     1998 |
|    11 | Cookies |    15450 |
|    12 | Cookies |    12579 |
|    12 |    Eggs |     4246 |