How to count multiple columns in a MySQL query

Question

I have two tables for saving user queries in my site. One for Tickets and the other for Ticket Replies. What I want is to count total tickets + total replies + total tickets with different status's. I can successfully count total tickets and total replies but I am having problem with counting ticket status's.

The problem: When I use following query, it shows total new and open tickets but as the query is a JOIN query, it shows total status's of all rows (which means it's showing sum of status's for all replies and not all tickets).

SELECT  COUNT(tr.ticketid) totalReplies, 
    COUNT(DISTINCT(t.ticketid)) totalTickets,
        SUM(CASE WHEN t.status='Open' THEN 1 END) totalOpen,
        SUM(CASE WHEN t.status='Close' THEN 1 END) totalClose
FROM `tbl_ticket` t 
INNER JOIN `tbl_ticket_reply` tr ON tr.ticketid = t.ticketid

Hope I am making myself clear. I will explain it with an example. Let's say we have total following records:

total tickets: 10
total replies: 20

Let's say, half tickets are opened and half closed. Then it should show 5 open, 5 closed. But instead, it gives 10 open and 10 closed.

Update: I do not want to group my query by status as it would then distribute my total tickets among status's.

Any help? Thanks

concept104 · Accepted Answer · 2014-03-05 10:50:01Z

2

this code may work for your data

SELECT  
COUNT(t.ticketid) AS totaltickets, 
SUM(CASE WHEN t.status='Open' THEN 1 END) AS totalopen, 
SUM(CASE WHEN t.status='Close' THEN 1 END) AS totalclose
,(
    SELECT COUNT(*) FROM tbl_ticket t 
    LEFT JOIN tbl_ticket_reply tr ON t.ticketid=tr.ticketid
) AS total_replies
FROM tbl_ticket t

answered Mar 5, 2014 at 10:50

concept104

891 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravinder Reddy · Accepted Answer · 2014-03-05 10:23:33Z

1

Use GROUP BY status in your query.

You need something like:

select count(tickets) tkt_cnt, count(replies) rep_cnt from mytable
group by status

On your query, following change should be working.

SELECT
    COUNT(tr.ticketid) totalReplies, 
    COUNT(DISTINCT(t.ticketid)) totalTickets,
    SUM(CASE WHEN t.status='Open' THEN 1 ELSE 0 END) totalOpen,
    SUM(CASE WHEN t.status='Close' THEN 1 ELSE 0 END) totalClose
FROM `tbl_ticket` t 
INNER JOIN `tbl_ticket_reply` tr ON tr.ticketid = t.ticketid
GROUP BY t.status

edited Mar 5, 2014 at 10:23

answered Mar 5, 2014 at 10:17

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

1 Comment

Raja Amer Khan Over a year ago

Hi. Thanks. But, I don't want to group by status as it would then show 2 rows for total tickets and total replies. I don't want my tickets and replies to be distributed among status.

Collectives™ on Stack Overflow

How to count multiple columns in a MySQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related