Counting records

[dcjones]

Hi all.

I have an SQL statement that performs 95% of the requirement I need, ut I am having promlems with the last 5%.

My query looks up the data in two tables like so:

SELECT 
c.coupon_id, 
c.code, 
c.used_count, 
c.masteragent,
p.masteragent,
p.product_id,
SUM(p.amount)
FROM coupon c LEFT JOIN payments p
ON c.coupon_id = p.coupon_id
WHERE c.masteragent = $_SESSION['m']
GROUP BY c.used_count, p.product_id

What I want to do is perform a count on the p.product_id, i.e how many records for each record in the payments table and place the results into temp filed that I can use.

I hope I have written this clearly.

[kakki]

like this ???

SELECT
c.coupon_id,
c.code,
c.used_count,
c.masteragent,
p.masteragent,
p.product_id,
sum(p.amount),
COUNT(p.product_id) AS _count
FROM coupon c LEFT JOIN payments p
ON c.coupon_id = p.coupon_id
WHERE c.masteragent = $_SESSION['m']
GROUP BY c.used_count, p.product_id 

you can also use COUNT(DISTINCT(p.product_id)) ...

btw:
you group by on used_count, that could make the result ( COUNT..... ) unusable