How to set Query into variable in MySql

Question

I want to find the item names for the cheapest and most expensive items for each group. But I don't know why I have SQL error (1064) near " SET @maxi := (SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode GROUP BY A.ItCode HAVING MAX(B.Price))". maybe there is something wrong or missing from my sql code

This is what I expecting for

vd code	vd name	cheapest item	cheapest price	Most expensive item	Most Expensive Price
Vd001	DEF Textile	Polyester	2600	Polyester	2600
Vd003	MNO Textile	Polyester	3228	Silk	3950
Vd004	XYZ Textile	Polyester	2800	Silk	3480

And here's my code

SET @mini :=(SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode 
                    GROUP BY A.ItCode HAVING MIN(B.Price)
                )
SET @maxi := (SELECT A.ItName FROM tblqtdtl B INNER JOIN tblitem A ON A.ItCode = B.ItCode 
                    GROUP BY A.ItCode HAVING MAX(B.Price)
                 )

SELECT V.VdCode, V.VdName, @mini AS 'Cheapest Item', MIN(B.Price) AS 'Cheapest Price', @maxi AS 'Most Expensive Item'
,MAX(B.Price) AS 'Most Expensive Price'
 
 FROM tblqthdr C 
 INNER JOIN tblvendor V ON C.VdCode = V.VdCode
 INNER JOIN tblqtdtl B ON C.DocNo = B.DocNo
 INNER JOIN tblitem A ON B.ItCode = A.ItCode
 WHERE V.ActInd='Y'
 GROUP BY V.VdCode

This is the Input script

tblqdtl

INSERT INTO `tblqtdtl` (`DocNo`, `ItCode`, `Price`) VALUES
    ('QT001', 'It004', 38700.00),
    ('QT002', 'It002', 25600.00),
    ('QT003', 'It005', 21000.00),
    ('QT004', 'It004', 34000.00),
    ('QT005', 'It003', 32000.00),
    ('QT006', 'It002', 31780.00),
    ('QT007', 'It001', 28000.00),
    ('QT008', 'It002', 21800.00),
    ('QT009', 'It004', 32600.00),
    ('QT010', 'It002', 27500.00);

tblitem

INSERT INTO `tblitem` (`ItCode`, `ItName`, `ItCtCode`) VALUES
    ('Itestt001', 'Cotton', 'ALA'),
    ('It002', 'Polyester', 'SIN'),
    ('It003', 'Wool', 'ALA'),
    ('It004', 'Silk', 'ALA'),
    ('It005', 'Rayon', 'SIN');

tblqhdr

INSERT INTO `tblqthdr` (`DocNo`, `DocDt`, `VdCode`) VALUES
    ('QT001', '20180102', 'Vd003'),
    ('QT002', '20180203', 'Vd001'),
    ('QT003', '20180203', 'Vd002'),
    ('QT004', '20180207', 'Vd004'),
    ('QT005', '20180304', 'Vd003'),
    ('QT006', '20180401', 'Vd003'),
    ('QT007', '20180701', 'Vd005'),
    ('QT008', '20180902', 'Vd002'),
    ('QT009', '20180902', 'Vd005'),
    ('QT010', '20181203', 'Vd004');

tblvendor

INSERT INTO `tblvendor` (`VdCode`, `VdName`, `ActInd`) VALUES
    ('Vd001', 'ABC Textile', 'Y'),
    ('Vd002', 'DEF Textile', 'N'),
    ('Vd003', 'GHI Textile', 'Y'),
    ('Vd004', 'JKL Textile', 'Y'),
    ('Vd005', 'MNO Textile', 'N');

please share the create table and insert scripts for sample data and output you need. — Amit Verma
– Amit Verma, Commented Jun 23, 2021 at 4:17
If you provide the error you are getting, it will also help. — Chandra Mochahary
– Chandra Mochahary, Commented Jun 23, 2021 at 4:21
HAVING MIN(B.Price) doesn't make any sense. HAVING needs to be followed by a true/false condition expression. Do you mean HAVING B.Price = MIN(B.Price)? — Barmar
– Barmar, Commented Jun 23, 2021 at 4:35
This is what I expecting for Price values in desired output does not match ones in sample data. — Akina
– Akina, Commented Jun 23, 2021 at 4:59

Akina · Accepted Answer · 2021-06-23 05:05:02Z

1

SELECT DISTINCT
       VdCode,
       VdName,
       FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price ASC) `Cheapest item`,
       MIN(Price) OVER (PARTITION BY VdCode) `Cheapest price`,
       FIRST_VALUE(ItName) OVER (PARTITION BY VdCode ORDER BY Price DESC) `Most expensive item`,
       MAX(Price) OVER (PARTITION BY VdCode) `Most Expensive Price`
FROM ( SELECT *
       FROM tblqthdr C 
       INNER JOIN tblvendor V USING (VdCode)
       INNER JOIN tblqtdtl B USING (DocNo)
       INNER JOIN tblitem A USING (ItCode)
       WHERE V.ActInd='Y' ) subquery

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=53cade7afe509dcde6f259e5c5ae3fa3

answered Jun 23, 2021 at 5:05

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to set Query into variable in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related