CODEWITHSUNDEEP
mysqlsqlsubqueryaggregate-functionswhere-clause
Mukesh
Mukesh

Reputation: 963

mysql GROUP CONCAT not returning values

Here is my query

SELECT 
    SUM(o.order_disc + o.order_disc_vat) AS manualsale
FROM
    orders o
WHERE
    o.order_flag IN (0 , 2, 3)
        AND o.order_status = '1'
        AND (o.assign_sale_id IN (SELECT GROUP_CONCAT(CAST(id AS SIGNED)) AS ids FROM users WHERE team_id = 92))
        AND DATE(o.payment_on) = DATE(NOW())

above query return null when i run this query in terminal

When i use subquery below it returns data

SELECT GROUP_CONCAT(CAST(id AS SIGNED)) AS ids FROM users WHERE team_id = 92)

above query returns

'106,124,142,179'

and when i run my first query like below

SELECT 
    SUM(o.order_disc + o.order_disc_vat) AS manualsale
FROM
    orders o
WHERE
    o.order_flag IN (0 , 2, 3)
        AND o.order_status = '1'
        AND (o.assign_sale_id IN (106,124,142,179))
        AND DATE(o.payment_on) = DATE(NOW())

it return me value. Why it is not working with subquery please help

Upvotes: 0

Views: 310

Answers (1)

GMB
GMB

Reputation: 222632

This does not do what you want:

AND (o.assign_sale_id IN (SELECT GROUP_CONCAT(CAST(id AS SIGNED)) AS ids FROM users WHERE team_id = 92))

This compares a single value against a comma-separated list of values, so it never matches (unless there is just one row in users for the given team).

You could phrase this as:

AND assign_sale_id IN (SELECT id FROM users WHERE team_id = 92)

But this would probably be more efficently expressed with exists:

AND EXISTS(SELECT 1 FROM users u WHERE u.team_id = 92 AND u.id = o.assign_sale_id)

Side note: I would also recommend rewriting this condition:

AND DATE(o.payment_on) = DATE(NOW())

To the following, which can take advantage of an index:

AND o.payment_on >= current_date AND o.payment_on < current_date + interval 1 day

Upvotes: 2

Related Questions