Mysql – most efficient way to retrieve data based on multiple selects and wheres

I'm having trouble finding the most efficient way of retrieving various different sumed values from a Mysql table.

Let's say I've got 4 columns - userid, amount, paid, referral.

I'd like to retrieve the following based on a user id:

1 - the sum of amount that is paid (marked as 1)
2 - the sum of amount that is unpaid (marked as 0)
3 - the sum of amount that is paid and referral (marked as 1 on both paid and referral columns)
4 - the sum of amount that unpaid and referral (marked as 0 on paid and 1 on referral columns)

I've tried an embedded select statement like this:

SELECT  (
    SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 0 and userid = '100'
) AS unpaid
(
    SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 1 and userid = '100'
) AS paid,
(
SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 0 and isreferral = 1 and userid = '100'
) AS refpending,
(
SELECT sum(payout)
    FROM   table1
    WHERE ispaid = 1 and isreferral = 1 and userid = '100'
) AS refpaid

This works, but its slow (or at least feels like it could be quicker) on my server, around 1.5 seconds.

I'm sure there is a better way of doing this with a group statement but can't get my head around it!

Any help is much appreciated.

Thanks



Read more here: https://stackoverflow.com/questions/67008230/mysql-most-efficient-way-to-retrieve-data-based-on-multiple-selects-and-wheres

Content Attribution

This content was originally published by Ed Jones at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: