CODEWITHSUNDEEP
mysql
Chamath Gunasekara
Chamath Gunasekara

Reputation: 129

how to get Mysql COUNT within subquery

here i have a table named as test2. i need to get the below output from a single mysql query.i have tried it using a subquery but i was fail. kindly help me to sort this.

pending status- 154

completed status - 159

enter image description here

required output

enter image description here

query that i have tried 

SELECT
    (
        test2.doc_no,
        SELECT
            (
                Count(test2.esn) AS pending_quantity,
                test2.doc_no
            FROM
                test2
            WHERE
                test2.sttus = 154
            GROUP BY
                test2.doc_no
            ),
            SELECT
                (
                    Count(test2.esn) AS completed_quantity,
                    test2.doc_no
                FROM
                    test2
                WHERE
                    test2.sttus = 159
                GROUP BY
                    test2.doc_no
                )
    )

Upvotes: 0

Views: 32

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7947

SELECT doc_no,
SUM(STATUS=154) AS pending_quantity,
SUM(STATUS=159) AS completed_quantity
FROM 
test2 GROUP BY doc_no

Try above query.

Upvotes: 1

Related Questions