How can I get SQL COUNT's with different conditions?

Question

I want to display some statistics to users that entered a command and I'm using to different SQL queries for that.

The first one:

SELECT COUNT(*) FROM guild_finished_giveaways WHERE guild_id = {} AND winner_id LIKE '%750718563651944518%'

I want to get the giveaway win count from this specific user on the specified guild.

But in the second query, I want the same, except for the specified guild. It should count all entries, like a "global" giveaway win count.

The second one:

SELECT COUNT(*) FROM guild_finished_giveaways winner_id LIKE '%750718563651944518%'

How can I combine them into only run one query? I need this solution to improve my code performance and reduce code.

what does winner_id look like? it will be much more efficient if you are matching a prefix of the column value and it is index, or you have an indexed virtual column with the guild id if it needs to be extracted from json or something — ysth, Jan 09 '23 at 20:06

score 2 · Accepted Answer · answered Jan 09 '23 at 19:49

2

You can use SUM() instead.

SELECT SUM(guild_id = {} AND winner_id LIKE '%750718563651944518%') as guild_winer ,
       SUM(winner_id LIKE '%750718563651944518%') as win_sum
FROM guild_finished_giveaways ;

answered Jan 09 '23 at 19:49

Ergest Basha

7,870
4
8
28

score 1 · Answer 2 · answered Jan 09 '23 at 19:52

Use conditional aggregation:

SELECT COUNT(CASE WHEN guild_id = {} THEN 1 END) AS count_id,
       COUNT(*) AS count_all
FROM guild_finished_giveaways 
WHERE winner_id LIKE '%750718563651944518%';

or:

SELECT SUM(guild_id = {}) AS count_id,
       COUNT(*) AS count_all
FROM guild_finished_giveaways 
WHERE winner_id LIKE '%750718563651944518%';

How can I get SQL COUNT's with different conditions?

2 Answers2