1

For example, if I had a table of DVDs people owned, like this

  OWNER               TITLE
---------------------------------
Jennifer    |    Ghostbusters
Jennifer    |    Jurassic Park
Alex        |    Titanic
Jennifer    |    Aliens
Jack        |    Seinfeld
Jack        |    Baby's Day Out
Alex        |    Jurassic Park

And I wanted to get a count of how many rows each OWNER had, like

Jennifer    |    3
Alex        |    2
Jack        |    2

How would I do that? I know how to count how many OWNERs there are, but not how many rows each OWNER has. So far I've been using multiple statements -- select owner(distinct owner), make an array of the returned names, then issue a "select count from table where owner = array[i]" for each item in the array -- but that doesn't seem like the smart way of doing it.

takendarkk
  • 3,347
  • 8
  • 25
  • 37
GreenTriangle
  • 2,382
  • 2
  • 21
  • 35
  • 1
    If you're already comfortable with the `COUNT()` aggregate for _all rows_, you just need to add the `GROUP BY OWNER` to group per `OWNER`. – Michael Berkowski Feb 21 '14 at 02:52
  • What does "unique" have to do with your question? Might the same title be owned by the same owner, and in those cases you want the title counted only once? Your example data doesn't have any situations where the same owner owns 2+ copies of the same movie, so it's unclear whether you actually need a distinct count of movies. – Brian DeMilia Feb 21 '14 at 03:02

4 Answers4

2
select count(1) as COUNT,OWNER from DVDS group by OWNER
Aaron
  • 55,518
  • 11
  • 116
  • 132
Raymond Cheng
  • 2,425
  • 21
  • 34
2

I think that having the following query: select count (1) from dvds group by owner would solve this. This is assuming that dvds is your table's name

lucasnadalutti
  • 5,818
  • 1
  • 28
  • 48
2

Use the count function and GROUP BY the column you want to group by.

SELECT OWNER, COUNT(*)
FROM DVD
GROUP BY OWNER
Tom
  • 7,640
  • 1
  • 23
  • 47
0

Number of DVDs by owner:

select owner, count(*) from dvd group by owner

Number of unique DVDs by owner:

select owner, count(*)
  from (select distinct owner, title from dvd) x
 group by owner
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33