Debraj Chatterjee Debraj Chatterjee - 1 year ago 65
MySQL Question

Count rows having status "closed" and status "unclosed" for every month in a year

This is the table, I need to get count of rows for status cloesd and status unclosed for every month in a year. Thanks in advance.

The table image

enter image description here

A J A J
Answer Source

You can try following query.

SELECT *
FROM 
(
    (
        SELECT MONTH(mt1.calltime) AS MONTH, YEAR(mt1.`calltime`) AS YEAR, COUNT(mt1.`status`) AS closed, 0 AS unclosed
        FROM myTable mt1
        WHERE mt1.`status`='closed'
    )
    UNION ALL
    (
        SELECT MONTH(mt2.calltime) AS MONTH, YEAR(mt2.`calltime`) AS YEAR, 0 AS closed, COUNT(mt2.`status`) AS unclosed
        FROM myTable mt2
        WHERE mt2.`status`='unclosed'
    )
) AS tablea
GROUP BY tablea.month

Replace myTable with your tablename.

MONTH will extract month of the year from the given date, and YEAR will give year.

SQL FIDDLE

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download