CODEWITHSUNDEEP
sqlpostgresqlhibernatespring-boot
user320676
user320676

Reputation: 384

How to combine multiple sql queries into single query?

I am working on spring boot framework with postgressql. Below are example queries 1,2,...n. These queries are working fine. But I have to combine these queries into single query because table is having huge data so I can not run the query multiple times(in a for loop) due to performance issue. But I am not getting any idea to combine these query.

1. SELECT product_name, count(*) FROM Products WHERE product_name='pro_a1' and price between 20 and 30 group by product_name;

2. SELECT product_name, count(*) FROM Products WHERE product_name='pro_b1' and price between 50 and 70 group by product_name;

I want to combine above queries into one something like below which i am unable to achieve.

SELECT product_name, count(*) FROM Products WHERE product_name in("pro_a1", "pro_b1", ...) and price between (20,30) AND (50,70) AND so on. group by product_name;

Any idea to do the task. I am a beginner level developer in spring and hibernate world. Please suggest a solution or any helpful link.

Thanks in advance

Upvotes: 0

Views: 490

Answers (3)

capcj
capcj

Reputation: 1535

SELECT product_name, count(*)
FROM Products 
WHERE product_name='pro_a1' OR product_name='pro_b1' 
AND price between 20 and 30 OR price between 50 and 70 group by product_name;

I think this is the better approach, simple and clear. You can use IN statements TOO, the performance is similar to low number of filters.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

This is one method:

SELECT product_name, count(*)
FROM Products
WHERE (product_name = 'pro_a1' and price between 20 and 30) OR
      (product_name = 'pro_b1' and price between 50 and 70)      
GROUP BY product_name;

If you then want separate counts for each product:

SELECT product_name, count(*),
       SUM( (product_name = 'pro_a1' and price between 20 and 30)::int) as cnt_a1,
       SUM( (product_name = 'pro_b1' and price between 50 and 70)::int) as cnt_b1
FROM Products
WHERE (product_name = 'pro_a1' and price between 20 and 30) OR
      (product_name = 'pro_b1' and price between 50 and 70)      
GROUP BY product_name;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use conditional aggregation.

SELECT product_name
,count(case when product_name='pro_a1' and price between 20 and 30 then 1 end)
,count(case when product_name='pro_b1' and price between 50 and 70 then 1 end) 
FROM Products 
group by product_name;

Upvotes: 1

Related Questions