0

Given below table(Product)

id|name|price_type|price|
--|----|----------|-----|
 1|book|MRP       |   10|
 2|book|SALE      |    5|
 3|pen |MRP       |   10|

In this table the answer is 1, since only one product "book" is having sale price 50% of mrp.

I tried doing the following query, but it is giving error

select name from product group by name HAVING  (count(product.name)>1 && product.price_type="MPR" && product.price_type="SALE");
1
  • You might also want to consider what to do if your sale price isn't exactly 50% of MRP - you might want a query which gives you the number of items that are simply below MRP?
    – Vérace
    Apr 9, 2020 at 14:36

2 Answers 2

1

In order to (finally) solve your problem, I did the following

CREATE TABLE product
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR (15) NOT NULL,
  price_type VARCHAR (10) NOT NULL,
  price INTEGER NOT NULL
);

Populate the table - I've added extra records, because, as I've found (again), testing with 1 record (or set - i.e. name = 'book') - I added books 1, 2 and 3, because I doubt if you're only selling one book and pens 1 and 2.

INSERT INTO product (name, price_type, price)
VALUES 
('book1', 'MRP',  10),
('book1', 'SALE',  5),
('pen1', 'MRP',  10),
('pen1', 'MRP',  5),

('book2', 'MRP',  14),
('book2', 'SALE',  7),
('pen', 'MRP',  16),

('book3', 'MRP',  30),
('book3', 'SALE',  15),
('pen', 'MRP',  8);

The query

SELECT SUM(cnt) AS number_of_50
FROM
(
  SELECT  p1.name, COUNT(p1.name) AS cnt
  FROM product p1
  JOIN product p2
    ON p1.name = p2.name
  WHERE p1.price = 2 * p2.price
  GROUP BY p1.name
) AS tab

Result

number_of_50
5

dbfiddle here!

p.s. Apart from having more than one record, you might consider using a fiddle for your tables and data for any questions in the future? Also, please also always supply your version of MySQL, it can make a real difference to the answer. There are some articles on how to ask questions here on my profile - you might want to take a look? Welcome to the forum!

0

You can Do it with

select name 
from product 
group by name 
HAVING  count(product.name)>1 
        AND (SUM(product.price_type="MRP") + SUM(product.price_type="SALE")) = 2

SUM(product.price_type="MRP") returns 1 if the condition is met

And when bith are present you get your 2

CREATE TABLE product (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price_type` VARCHAR(4),
  `price` INTEGER
);

INSERT INTO product
  (`id`, `name`, `price_type`, `price`)
VALUES
  ('1', 'book', 'MRP', '10'),
  ('2', 'book', 'SALE', '5'),
  ('3', 'pen', 'MRP', '10');
✓

✓
select name 
from product 
group by name 
HAVING  count(product.name)>1 AND (SUM(product.price_type="MRP") + SUM(product.price_type="SALE")) = 2
| name |
| :--- |
| book |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.