CODEWITHSUNDEEP
mysql
Dre
Dre

Reputation: 151

MySQL Query Count two Fields

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?

Upvotes: 1

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424993

Use distinct inside the count:

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

Upvotes: 1

Related Questions