CODEWITHSUNDEEP
phpmysqlsql
KmanOfficial
KmanOfficial

Reputation: 77

MySQL where SUM(value) is more than a specific value in a UNION query

the following query doesn't work:

(
    SELECT *
    FROM `items`
    WHERE `name` LIKE '%SCAR%' AND `rarity` = 'Legendary'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 1
)

UNION

(
    SELECT *
    FROM `items`
    WHERE `rarity` = 'Legendary'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 3
)

UNION

(
    SELECT *
    FROM `items`
    WHERE `rarity` = 'Epic'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 1
)

GROUP BY
    weight
HAVING
    SUM(value) < 100

ORDER BY
    RAND()

This works alone, however:

(
    SELECT *
    FROM `items`
    WHERE `name` LIKE '%SCAR%' AND `rarity` = 'Legendary'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 1
)

UNION

(
    SELECT *
    FROM `items`
    WHERE `rarity` = 'Legendary'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 3
)

UNION

(
    SELECT *
    FROM `items`
    WHERE `rarity` = 'Epic'
    ORDER BY weight / RAND( ) DESC
    LIMIT 0, 1
)

ORDER BY RAND()

How would I go about adding condition that all the rows selected from the database have a sum of value field of at least a set value such as 1000? Thanks

Upvotes: 0

Views: 85

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

SELECT stuff 
FROM (unions) AS u 
GROUP BY u.somefield 
HAVING SUM(u.anotherfield) < somevalue
;

Make the unions a subquery. But note that selecting non-aggregated, non-grouped fields is generally frowned upon, disallowed by all but MySQL (and even it's most recent versions' default configurations do not allow it), and probably a very bad idea for this kind of query.

Also, RAND() does not always behave the way you think it should. In your UNIONed queries you will probably be dividing each weight by a different RAND() value (since it has to be evaluated for each weight); but your final ORDER BY RAND() could be ordering all results using the same single RAND() value since mysql may take a shortcut and only evaluate it once.

Upvotes: 1

Related Questions