CODEWITHSUNDEEP
mysql
Kane
Kane

Reputation: 11

Combining and adding like results

I have searched all over trying to find away when multiple like records are retrieved I need to add together 1 field. My query and results below.

This is my query:

SELECT m.id as 'id', m.name as 'Merchant Name', c.numStamps as 'Num Stampts on Card', COUNT(c.numStamps) as '# Users with this # Stampts'
FROM Merchants m, Cards c, Stores s
WHERE c.store_id = s.id
AND s.merchant_id = m.id
and c.redeemed = 0
and c.Numstamps>8
and c.datelaststamp > '2011-07-16 07:00:00'
group by m.id, c.numStamps
order by m.name, c.numStamps

These are the results:

id Merchant Name Stampts UserStampts

391 360 Gourmet Burritos 9 3
391 360 Gourmet Burritos 10 6

I need to combine id and Merchant Name. Stampts does not really matter but I need to add together UserStampts. So I need the return to be:

391 360 Gourmet Burritos 10 9

Upvotes: 1

Views: 43

Answers (3)

endyourif
endyourif

Reputation: 2202

You need to group by the name as well:

group by m.id, m.name, c.numStamps

Upvotes: 0

LSerni
LSerni

Reputation: 57408

A simple way to modify the query would be to turn it into a nested query:

SELECT id, Merchant, SUM(Stampts) AS Stampts, SUM(UserStampts) AS UserStampts
FROM ( ... your old query goes here ... ) AS oldquery
GROUP BY id, Merchant;

This will sum both columns (independently).

Upvotes: 1

iJade
iJade

Reputation: 23801

Try this

SELECT m.id as 'id', m.name as 'Merchant Name', c.numStamps as 'Num Stampts
on Card', COUNT(c.numStamps) as '# Users with this # Stampts'

FROM Merchants m, Cards c, Stores s

WHERE c.store_id = s.id

AND s.merchant_id = m.id

and c.redeemed = 0

and c.Numstamps>8

and c.datelaststamp > '2011-07-16 07:00:00'

group by m.id

order by m.name, c.numStamps

Upvotes: 0

Related Questions