How do I check if a field exist in a group?-CodePudding

I have a bid_trans table that cotains some fields like Price, Car_Id, Bid_type, Account_id

SELECT bt.price AS price, ((COUNT(IF(bt.status = 3, 1, NULL)) / COUNT(bt.id)) * 100) AS rate
            FROM bid_trans bt
            WHERE bt.car_id = '451a8ad6-d31d-4e89-84df-498afcb6da15' AND bt.bid_type = 1
            GROUP BY bt.price
            ORDER BY bt.price DESC
            LIMIT 10;

This is a basic group query. My problem now is how can I boolean field to check if a account_id exists in a group of price or not?

Price | Rate | isJoined
10         5       0
20        10       1

Like the isJoined column above, with different account_id, it will display different result

CodePudding user response：

If i get your question right you want isJoined to be 1 if one specific account_id is contained in the group.

This can be done by using the SUM of account_id = <insert_account_id> and test if its greater than 0.

SELECT bt.price AS price,
       ((COUNT(IF(bt.status = 3, 1, NULL)) / COUNT(bt.id)) * 100) AS rate,
       SUM(account_id = <insert_account_id>) > 0 AS isJoined
FROM bid_trans bt
WHERE bt.car_id = '451a8ad6-d31d-4e89-84df-498afcb6da15' AND bt.bid_type = 1
GROUP BY bt.price
ORDER BY bt.price DESC
LIMIT 10;