Filtering using WHERE for a subquery-CodePudding

i consider myself a pretty well-versed developer, but this one has me stumped.

The actual use case is somewhat more complicated than this (i have built a data-view framework that allows you to filter and search data), but at its simplest...

Why can't I do something like this?:

SELECT
    fundraisers.id,
    (
        SELECT
            count(*)
        FROM
            transactions
        WHERE
            transactions.fundraiser_id = fundraisers.id) AS total
    FROM
        fundraisers
    WHERE
        total > 331
    ORDER BY
        total DESC

I've also tried:

I'm aware i can successfully use HAVING to do this, but i need it to be part of the WHERE clause in order to be able to use it in conjunction with other filters using the right AND/OR conditions.
doing it as a subquery JOIN instead, but it never seems to return the right count of transactions for the row.

Any help is appreciated! Thanks folks.

CodePudding user response：

You can use a derived table, in other words a subquery in the FROM clause instead of the select-list.

SELECT t.fundraiser_id, t.total
FROM
    fundraisers AS f
JOIN (
    SELECT fundraiser_id, COUNT(*) AS total
    FROM transactions
    GROUP BY fundraiser_id
) AS t ON t.fundraiser_id = f.id
WHERE
    t.total > 331
ORDER BY
    t.total DESC;

The reason you can't refer to an alias in the WHERE clause is that the conditions of the WHERE clause is evaluated before expressions in the select-list are evaluated. This is a good thing, because you wouldn't want the select-list to be evaluated for potentially millions of rows that would be filtered out by the conditions anyway. Evaluating the select-list only for rows that are included in the result helps improve performance.

But it means the result of those expressions in the select-list, and their alias, isn't available to be referenced by conditions in the WHERE clause.

The workaround I show in my example above produces the results in a subquery, which happens before the WHERE clause gets to filter the results.

CodePudding user response：

i don't know what do you want to select but try this

select fundraisers.id,count(*) as total FROM
        fundraisers f join transactions t on t.fundraiser_id=f.fundraiser_id
    WHERE
        total > 331
    ORDER BY
        total DESC