CODEWITHSUNDEEP
mysqlcount
user1547410
user1547410

Reputation: 893

MYSQL multiple counts on table with joins

Ok so i have this SQL query

$query = "
            SELECT
                c.category, count(t.id_ticket) as ticket_count, count(tm.id_message) as message_count
            FROM
                tickets t
            LEFT JOIN
                ticketMessages tm
            ON
                t.id_ticket = tm.id_ticket
            LEFT JOIN
                categories c
            ON
                t.id_category = c.id_category
            GROUP BY
                t.id_category
        ";

So basically i have a ticket table, each ticket can have multiple messages and each ticket has one category its assigned to.

Now lets assume the following

I have 2 tickets in the red category and there are 5 messages in total I have 4 tickets in the blue category and there are 10 messages in total I have 3 tickets in the black category and there are 7 messages in total

What i want to do is show something like this:

[0]=>
  array(4) {
    ["category"]=>
    string(12) "Red"
    ["id_ticket_count"]=>
    string(1) "2"
    ["id_message_count"]=>
    string(1) "5"
  }
[1]=>
array(4) {
  ["category"]=>
  string(12) "Blue"
  ["id_ticket_count"]=>
  string(1) "4"
  ["id_message_count"]=>
  string(2) "10"
}
[1]=>
array(4) {
  ["category"]=>
  string(12) "Black"
  ["id_ticket_count"]=>
  string(1) "3"
  ["id_message_count"]=>
  string(2) "7"
}

However my SQL returns the same value for both messages and tickets, that value is messages but i also want to know how many tickets. I assumed counting id_ticket.

If i add group by t.ticket_id then it shows a new array item for every ticket, so i end up with 2 red category items, 4 blue and 3 black items but it should only show 1 item per category with the counts for each category.

If it helps the tables would look something like this

Category
id_category | category
1 | red
2 | blue
3 | black

Tickets
id_ticket | id_category 
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 2
7 | 3
8 | 3
9 | 3

Messages
id_message | id_ticket
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 3
7 | 3
8 | 3
9 | 4
10 | 4
11 | 4
12 | 5
13 | 5
14 | 5
15 | 6
16 | 7
17 | 7
18 | 8
19 | 8
20 | 9
21 | 9
22 | 9

Upvotes: 3

Views: 104

Answers (5)

Michael Kanios
Michael Kanios

Reputation: 59

Count will return the total number of rows returned for each group specified in the group by.

Count(Distinct "fieldName") will return the distinct value count for the field specified. In your case: count(t.id_ticket), count(tm.id_message) or even count(*) would return the same value (the number of rows for each group).

From the give SQL statement you should also be careful with the GROUP BY clause, since MySql will allow you to use strange group combinations! In general have in mind that whatever columns you have in the Select clause and are not aggregated should be included in the group by clause.

Upvotes: 0

Omar Alves
Omar Alves

Reputation: 763

You might try to use a CASE WHEN clause to handle cardinality issues more properly. As far I know using count can lead to NULL values due those tickets those have no messages assigned to, be counted as 1 misleading valid row.

Maybe something like 

           SELECT
            c.category, count(t.id_ticket) as ticket_count,
            SUM(CASE WHEN tm.id_message IS NOT NULL THEN 1 ELSE 0 END) as message_count
        FROM
            tickets t
        LEFT JOIN
            ticketMessages tm
        ON
            t.id_ticket = tm.id_ticket
        LEFT JOIN
            categories c
        ON
            t.id_category = c.id_category
        GROUP BY
            t.id_category

I'm assuming there's a 1:many relationship between tickets and ticketMessages.

Upvotes: 1

KaeL
KaeL

Reputation: 3659

So basically i have a ticket table each ticket can have multiple messages

I am assuming that each ticket can have messages, and can have no messages at all.

and each ticket has one category its assigned to.

And that all tickets should have their own corresponding category.

Following the result that you want, you can use this query:

SELECT C.category
  , COUNT(DISTINCT T.id_ticket) AS id_ticket_count
  , COUNT(M.id_ticket) AS id_message_count
  FROM Category C
  INNER JOIN Tickets T ON T.id_category = C.id_category
  LEFT JOIN Messages M ON M.id_ticket = T.id_ticket
  GROUP BY C.id_category

Please check the result in SQL Fiddle Demo.

Upvotes: 0

abeyaz
abeyaz

Reputation: 3164

You can use subquery for lowest level table to achieve this:

SELECT  c.category, 
    count(t.id_ticket) as ticket_count,
    SUM((SELECT COUNT(*) FROM ticketMessages WHERE id_ticket =  t.id_ticket)) as message_count
    FROM tickets t
    INNER JOIN categories c ON t.id_category = c.id_category
    GROUP BY t.id_category

Upvotes: 0

DRapp
DRapp

Reputation: 48139

This SHOULD get you what you are looking for. You want DISTINCT tickets at the same time you are getting the TOTAL count of messages

SELECT
      c.category,
      COUNT( distinct t.id_ticket ) as UniqueTickets,
      COUNT(tm.id_ticket) TotalMsgsPerTickets
   from
      Categories c
         LEFT JOIN tickets t
            ON c.id_category = t.id_Category
            left join ticketMessages tm
               ON t.id_ticket = tm.id_ticket
   group by
      c.id_Category

Upvotes: 0

Related Questions