CODEWITHSUNDEEP
mysqlsqlsql-server
Prometheus
Prometheus

Reputation: 33655

Get count based on two id

How can I get back a 'count' from my redeemed table based on both incentiveID and recipientID and add it to the following query.

SELECT recipient.*, incentive.*
            FROM recipient 
            INNER JOIN incentive ON recipient.incentiveID=incentive.incentiveID
            WHERE recipient.mobile = :mobile 
            AND recipient.incentiveID = :incentiveID"

The query below brings back recipient and incentive data. I would like to extend this and have a ‘count’ on how many times a recipient has redeem. i.e. redeemCount

Redeemed Table Looks like this and is linked by incentiveID ,recipientID 

RedeemedID incentiveID recipientID timestamp

Upvotes: 0

Views: 72

Answers (1)

David W
David W

Reputation: 10184

If you want a count of redemptions per employee by incentive, you can try something like

select a.recipientID,a.incentiveID,count(*)
  from redeemed a
 group by a.recipientID,a.incentiveID

If you want to add a count of all redemptions, you can't get it into your current query as you'd have to include elements of the incentive table to your GROUP BY clause. You can create a query to total redemptions per individual with something like this:

select a.receipientID, count(*)
  from redeemed a
 group by a.recipientID

You can then join that back to the recipient table to get things like a user name, or other relevant per-user information.

Hope that's helpful.

Upvotes: 1

Related Questions