MySQL Query Count two Fields

Question

I have a database table (notes) that has three columns: id, ticket, and user.

I need to show how many notes were entered by each user as well as how many unique tickets those notes were added to (grouped by user). For example, John Doe entered 100 notes into 50 unique tickets and Jane Doe entered 70 notes into 65 unique tickets. I'm not interested in which tickets they were added to, just how many.

Here is what I have so far.

select count(id) count
from notes
group by user

Is it possible to grab the number of unique tickets in the same query?

try to use distinct keyword :)

Last Breath
– Last Breath

2013-04-19 14:08:53 +00:00
Commented Apr 19, 2013 at 14:08 — Last Breath
– Last Breath, Commented Apr 19, 2013 at 14:08

Bohemian · Accepted Answer · 2013-04-19 14:11:12Z

1

Use distinct inside the count:

select
  user,
  count(id) note_count,
  count(distinct ticket) ticket_count
from notes
group by user

answered Apr 19, 2013 at 14:11

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2013-04-19 14:11:36Z

0

You are probably new to SQL. The query is something like:

select user, count(distinct TicketId) as NumTickets, count(id) as NumNotes
from notes
group by user

You should include the user in the select clause, so you know to whom the numbers apply. When naming things, it is a good idea to avoid reserved words like count, so I named on NumTickets and the other NumNotes.

answered Apr 19, 2013 at 14:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Query Count two Fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related