CODEWITHSUNDEEP
sqlsqlite
feminkk
feminkk

Reputation: 1225

SQL looping through a table to count and group

I have a table like this

 ID|list
  1|:A:B:C:
  2|:B:D:A:
  3|:C:A:D:

I want to count the number of each of A,B,C,D and group them as

A|3
B|2
C|2
D|2

I was thinking about iterating through the table and inserting values in to a temporary table from count statements like the following

select count(*) from table where list like %:A:%

It does look ugly.Is there a better way to do this?

(I am using sqlite from qt)

Upvotes: 2

Views: 524

Answers (1)

juergen d
juergen d

Reputation: 204854

Never, never, never store multiple values in one column! A better DB design would be

movies table
----------
id
name
release_date
...


actors table
------------
id
first_name
last_name
birth_date
...


movie_cast table
----------------
movie_id
actor_id

You could then select all actors of a specific movie like this

select a.lastName, a.first_name
from actors a
join movie_cast c on c.actor_id = a.id
join movies m on c.movie_id = m.id
where m.name = 'Citizen Kane'

Or select all movies a specific actor stars in like this

select m.name
from actors a
join movie_cast c on c.actor_id = a.id
join movies m on c.movie_id = m.id
where a.lastName ='Wayne'
and a.first_name 'John'

Upvotes: 2

Related Questions