CODEWITHSUNDEEP
sql
Adam Strudwick
Adam Strudwick

Reputation: 13129

SQL grouping results in a select

My SQL table "offers" contains offers users make for products (product_ID, customer_ID, offer).

In my admin page, I want to list the products for which at least one offer exists and show the total offers existing for it.

For example,

PRODUCT #324     Total offers: 42
PRODUCT #99      Total offers: 1
etc.

My guess would be to combine a SELECT DISTINCT product_ID FROM offers... And in a second query, to SELECT COUNT(*) FROM offers WHERE product_ID=...

Is it the most efficient way to achieve this, or is there a way to make it inside a single query?

Upvotes: 0

Views: 67

Answers (2)

MrSimpleMind
MrSimpleMind

Reputation: 8597

As bluefeet already answered, you achieve it in single query by using group by. (group by demo)

Another thing to mention is the order by, 

select
  product_id as id,
  count(*) as totals
from 
  t
group by product_id
order by totals;

If you want to sort with the totals of hits, or if you want to sort by product_id etc.

sqlfiddle

Upvotes: 1

Taryn
Taryn

Reputation: 247710

You can do this in one query which will get the count by grouping by the product_id:

SELECT product_ID, COUNT(*) 
FROM offers 
GROUP BY product_ID

Upvotes: 2

Related Questions