can someone help me with?
Have you tried the query on any database?
What is group by in SQL?
Which version of MySQL are you using ?
————- ———– ——-
| Motherboard | Asus | 3200 |
| Keyboard | Frontech | 550 |
| ZIP | Zebronics | 250 |
| Monitor | Samsung | 5000 |
| DVD | iBall | 900 |
| Printer | Epison | 2600
First of all this query will give syntax error as i.pro_name is not provided in group by clause and
Secondly this will bring 9 rows in output as you have mentioned i.pro_name hence it will consider each product and there own price as max price
Example: call order_status(2005, 11);
Secondly this will bring 9 rows in output as you have mentioned i.pro_name hence it will consider each product and there own price as max price
Copy paste the query , it gives the same result as you , only order by needs to be add and sequence of columns has to be adjusted
How mysql is allowing you to execute without mentioning i.pro_name in group by clause ?
What is group by in SQL? Learn ✅SQL Group By statement ✅Syntax for group by in SQL ✅Aggregate functions ✅Using group by on single & multiple columns, & more.
https://onecompiler.com/mysql/3y8khada4 try same thing here
If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function.
Example: call order_status(2005, 11);
CREATE DEFINER=`root`@`localhost` PROCEDURE `order_status`(year1 int,month1 varchar(3))
BEGIN
select orderNumber, orderDate, status from orders where year(orderdate)= year1
It’s showing 9 records bro
Same version
month(orderdate) = 01
create table item_mast (pro_id int, pro_name text, pro_price int, pro_com int);
insert into company_mast values (11,’Samsung’);
insert into company_mast values (12,’iBall’);
insert into company_mast values (13,’Epison’);
insert into company_mast values (14,’Zebronics’);
insert into company_mast values (15,’Asus’);
insert into company_mast values (16,’Frontech’);
insert into item_mast values (101,’Motherboard’,3200,15);
insert into item_mast values (102,’Keyboard’,450,16);
insert into item_mast values (103,’ZIP’,250,14);
insert into item_mast values (104,’Speaker’,550,16);
insert into item_mast values (105,’Monitor’,5000,11);
insert into item_mast values (106,’DVD’,900,12);
insert into item_mast values (107,’CD Drive’,800,12);
insert into item_mast values (108,’Printer’,2600,13);
insert into item_mast values (109,’Refill’,350,13);
with max_price as
(
select pro_com, max(pro_price) as price
from item_mast
group by pro_com
order by pro_com
)
select b.pro_name,a.price,c.com_name
from max_price a
join item_mast b on a.pro_com = b.pro_com and a.price = b.pro_price
join company_mast c on b.pro_com = c.com_id
order by a.price desc
Output:
pro_name | price | com_name
————- ——- ———–
Monitor | 5000 | Samsung
Motherboard | 3200 | Asus
Printer | 2600 | Epison
DVD | 900 | iBall
Speaker | 550 | Frontech
ZIP | 250 | Zebronics
@suricruise1 Can you check this now?
create table item_mast (pro_id int, pro_name text, pro_price int, pro_com int);
insert into company_mast values (11,’Samsung’);
insert into company_mast values (12,’iBall’);
insert into company_mast values (13,’Epison’);
insert into company_mast values (14,’Zebronics’);
insert into company_mast values (15,’Asus’);
insert into company_mast values (16,’Frontech’);
insert into item_mast values (101,’Motherboard’,3200,15);
insert into item_mast values (102,’Keyboard’,450,16);
insert into item_mast values (103,’ZIP’,250,14);
insert into item_mast values (104,’Speaker’,550,16);
insert into item_mast values (105,’Monitor’,5000,11);
insert into item_mast values (106,’DVD’,900,12);
insert into item_mast values (107,’CD Drive’,800,12);
insert into item_mast values (108,’Printer’,2600,13);
insert into item_mast values (109,’Refill’,350,13);
with max_price as
(
select pro_com, max(pro_price) as price
from item_mast
group by pro_com
order by pro_com
)
select b.pro_name,a.price,c.com_name
from max_price a
join item_mast b on a.pro_com = b.pro_com and a.price = b.pro_price
join company_mast c on b.pro_com = c.com_id
order by a.price desc
Output:
pro_name | price | com_name
————- ——- ———–
Monitor | 5000 | Samsung
Motherboard | 3200 | Asus
Printer | 2600 | Epison
DVD | 900 | iBall
Speaker | 550 | Frontech
ZIP | 250 | Zebronics
This okay bro
This one also correct.