Debraj Chatterjee Debraj Chatterjee - 1 month ago 6
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

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

Comments