CODEWITHSUNDEEP
mysqlsql
Gary Stewart
Gary Stewart

Reputation: 161

SQL getting values and then total count of those values

enter image description here I am working on an auction script with a table that looks like the attached image.

Within an auction two users can place the same bid amount, for example two players could place a bid of '1' which would give that bid amount a total count of two.

I need a query which gets all of a users single bid amount along with the total count of that bid amount within the scope of the auction id.

As it stands I first get the users bids:

SELECT bid_amount FROM all_bids WHERE auction_id = '129' AND user_id = '9'

And then I am looping through each amount in PHP and doing the following

SELECT COUNT(*) FROM all_bids WHERE auction_id = '129' AND bid_amount = 'xxx'

But this is of course very power hungry and I am sure it can be done all in one query.

I have got as far as

SELECT bid_amount,COUNT(*) FROM (SELECT bid_amount FROM all_bids WHERE auction_id = '129' AND user_id ='9') as foo GROUP BY bid_amount

Which returned the correct bid amounts but the counts were all 1 which is wrong as I guess my incorrect query is only counting the values within the subquery and not outside of the bids that just that user placed

Upvotes: 0

Views: 129

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want group by:

select bid_amount, count(*)
from all_bids 
where auction_id = 123 and player_id = 456
group by bid_amount;

Note that you do not need single quotes for a numeric constant.

Hmmm. You might want this phrased as:

select ab.bid_amount, count(*)
from all_bids ab
where ab.auction_id = 123 and
      ab.bid_amount in (select ab2.bid_amount
                        from all_bids ab2
                        where ab2.auction_id = ab.auction_id and ab2.player_id = 456
                       )
group by ab.bid_amount;

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

 SELECT  a.*, b.cnt from all_bids a 
 join (select bid_amount,COUNT(*) cnt from all_bids group by bid_amount) b 
 on a.bid_amount=b.bid_amount
 WHERE a.auction_id = '123' AND a.player_id = '456'

Upvotes: 1

Related Questions