CODEWITHSUNDEEP
mysql
Seth Pritchard
Seth Pritchard

Reputation: 77

Mysql Limiting by a count

I currently have a query I'm putting together to find the count of how many entries are made. I'm trying to find out how to limit those entries to anything below a certain count number ( < 4). 

SELECT
    t.id `Ticket ID`,
    a.asset `Asset`,
    sol.solution `Solution reported`,
    COUNT(tjou.id) `Journal entry count`

FROM tickets t 
    JOIN assets a ON t.asset_id = a.id
    LEFT JOIN ticket_solutions tsol ON tsol.ticket_id = t.id
    JOIN solutions sol ON tsol.solution_id = sol.id
    JOIN ticket_journal tjou ON tjou.ticket_id = t.id
WHERE t.deleted IS FALSE
AND tjou.system_msg IS FALSE
AND DATE(t.close_dt) >= DATE_SUB(NOW(),INTERVAL 1 MONTH)
AND DATE(a.labor_w_end_dt) > CURDATE()
AND sol.solution != 'Communications Issue'
GROUP BY t.id
ORDER BY `Journal entry count` ASC

Upvotes: 2

Views: 33

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

try using having (before order by )

SELECT
    t.id `Ticket ID`,
    a.asset `Asset`,
    sol.solution `Solution reported`,
    COUNT(tjou.id) `Journal entry count`

FROM tickets t 
    JOIN assets a ON t.asset_id = a.id
    LEFT JOIN ticket_solutions tsol ON tsol.ticket_id = t.id
    JOIN solutions sol ON tsol.solution_id = sol.id
    JOIN ticket_journal tjou ON tjou.ticket_id = t.id
WHERE t.deleted IS FALSE
AND tjou.system_msg IS FALSE
AND DATE(t.close_dt) >= DATE_SUB(NOW(),INTERVAL 1 MONTH)
AND DATE(a.labor_w_end_dt) > CURDATE()
AND sol.solution != 'Communications Issue'
GROUP BY t.id
HAVING COUNT(tjou.id) < 4
ORDER BY `Journal entry count` ASC

Upvotes: 0

rkosegi
rkosegi

Reputation: 14678

try to append this

HAVING COUNT(tjou.id) < 4

after GROUP BY and before ORDER statements

Reference.

Upvotes: 1

Related Questions