MySQL查询选择计数在组中的位置

Question

我有这样的数据：

| bid_id | created             | auction_id | user_id | bid_credits | bid_credits_free | bid_rating | balance | bidded_price | last_for_user | bid_ip       | bid_type |
+--------+---------------------+------------+---------+-------------+------------------+------------+---------+--------------+---------------+--------------+----------+
|    735 | 2013-10-11 10:02:58 |       9438 |   62323 |           1 |                0 |     0.0000 |  100333 |         0.86 | Y             | 72.28.166.61 | single   |
|    734 | 2013-10-11 10:02:56 |       9438 |   76201 |           1 |                1 |     0.0000 |    1115 |         0.85 | Y             | 72.28.166.61 | single   |
|    733 | 2013-10-11 10:02:55 |       9438 |   62323 |           1 |                0 |     0.0000 |  100334 |         0.84 | N             | 72.28.166.61 | single   |
|    732 | 2013-10-11 10:02:54 |       9438 |   76201 |           1 |                1 |     0.0000 |    1116 |         0.83 | N             | 72.28.166.61 | single   |
|    731 | 2013-10-11 10:02:52 |       9438 |   62323 |           1 |                0 |     0.0000 |  100335 |         0.82 | N             | 72.28.166.61 | single   |

我想把"bid_credits“和"bid_credits_free”作为单独的值.

因此，查询应该返回我：

| user_id | count(bid_credits) | count(bid_credits_free) |
+---------+--------------------+-------------------------+
|   62323 |                  3 |                       0 |
|   76201 |                  2 |                       2 |

我使用的查询是：

select user_id, count(bid_credits), count(bid_credits_free) from bids_history 
where auction_id = 9438 and user_id in (62323,76201) group by user_id;

但它没有正确计算出价..。有什么想法吗？

谢谢

Answer 1

你想把它们加起来，计数就是数行。试试这个：

select user_id, sum(bid_credits), sum(bid_credits_free) from bids_history 
where auction_id = 9438 and user_id in (62323,76201) group by user_id;

Answer 2

分组时使用和而不是计数。应起作用

我还修改了格式，以便更容易阅读:)

SELECT 
    user_id, 
    SUM(bid_credits), 
    SUM(bid_credits_free) 
FROM bids_history 
WHERE auction_id = 9438 AND user_id IN (62323,76201) 
GROUP BY user_id;

要使用sum而不是count的原因是，count只计算表中的行数，而不计算表中的内容/值。因此，当您按照这样的id进行分组时，您需要做一个求和来查看实际添加的内容。希望这有助于解释一些事情:)

Answer 3

请尝试以下查询：

请用和代替数..。

选择user_id，SUM(bid_credits)为bid_credits，SUM( bid_credits_free )为bid_credits_free，auction_id = 9438，user_id (62323,76201)为user_id；