Ilya Repenko Ilya Repenko - 10 months ago 46
PHP Question

How to return result of having expression in mysql at once

I have mysql query with subquery and aliases

SELECT
k.id,
k.name AS target,
k.account_id,
k.sub_name,
k.budget,
k.cost,
k.other_bids,
k.target AS statusin,
k.rank,
k.set_rank,
(SELECT
COUNT(kw.id)
FROM
keywords AS kw
WHERE
kw.crc = k.crc
GROUP BY kw.crc) AS count_kws,
(SELECT
SUM(IF(s.impressions, s.impressions, 0))
FROM
keywords AS kw
LEFT JOIN
stats AS s ON s.property_id = kw.property_id
AND s.dated BETWEEN '2017-06-17' AND '2017-08-16'
WHERE
kw.crc = k.crc
GROUP BY kw.crc) AS sum_impressions
FROM keywords as k
HAVING IF(ABS(sum_impressions),ROUND(ABS(sum_impressions) /
count_kws, 2),0) < 1


After executing query, I continue to execute the expression in having in php. How to return result of having expression in mysql at once

Answer Source

Wrap the query as subquery, add HAVING expression in SELECT statement and use it's alias in HAVING clause

SELECT
z.*,
IF(ABS(z.sum_impressions),ROUND(ABS(z.sum_impressions) / 
z.count_kws, 2),0) AS myexpr
FROM (
    SELECT 
    k.id,
    k.name AS target,
    k.account_id,
    k.sub_name,
    k.budget,
    k.cost,
    k.other_bids,
    k.target AS statusin,
    k.rank,
    k.set_rank,
    (SELECT 
        COUNT(kw.id)
    FROM
        keywords AS kw
    WHERE
        kw.crc = k.crc
    GROUP BY kw.crc) AS count_kws,
    (SELECT 
    SUM(IF(s.impressions, s.impressions, 0))
    FROM
        keywords AS kw
        LEFT JOIN
        stats AS s ON s.property_id = kw.property_id
        AND s.dated BETWEEN '2017-06-17' AND '2017-08-16'
    WHERE
        kw.crc = k.crc
    GROUP BY kw.crc) AS sum_impressions
    FROM keywords as k
) AS z
HAVING myexpr < 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download