MySQL how to get number of values from a user?

Question

I have this 3 tables:

Users:
user_id|user_nick
   1   |    a
   2   |    b

Category:
cat_id|cat_type
   1  |    a
   2  |    b
   3  |    c
   4  |    d

Meta:
met_id|met_name|met_user|met_type
   10 |  bla   |    1   |    1
   11 | blabla |    2   |    2
   12 |  foo   |    1   |    3
   13 | blafoo |    2   |    4
   14 | foofoo |    1   |    4
   15 | foobla |    1   |    4

How can I return something like this ?

user_id|met_type|total
   1   |   1    |  1
   1   |   2    |  0
   1   |   3    |  1
   1   |   4    |  2

For just one user and not for all of them.

met_type is a foreign key from Category.

I've tried like this but no success :/

SELECT met_user, met_type, COUNT(*) FROM Meta GROUP BY met_user WHERE met_user = '1'

Yeah, I think GROUP BY met_user isn't what you want to do here. The distinct groups are met_type with 1, 2, 3, 4, so. — Unihedron
– Unihedron, Commented Jun 21, 2014 at 3:48

Unihedron · Accepted Answer · 2014-06-21 04:16:45Z

2

Query:

SELECT met_user, met_type, count(*)
FROM Meta
WHERE met_user='1'
  GROUP BY met_type;

To get empty groups, you can use generateSeries() here:

SELECT m.met_user, g.meta_type, count(m)
FROM generate_series(1, 4) AS g(meta_type)
  LEFT OUTER JOIN Meta AS m
    ON m.met_user='1'
    AND m.met_type=g.meta_type
GROUP BY g.meta_type, m.met_user
ORDER BY g.meta_type;

Check it out! I made an sql fiddle.

edited Jun 21, 2014 at 4:16

answered Jun 21, 2014 at 3:46

Unihedron

11.1k13 gold badges65 silver badges72 bronze badges

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

4 Comments

Unihedron Over a year ago

Would you like empty met_type groups to show up too?

SnakeSheet Over a year ago

Thanks, I never thought about trying to group by met_type instead of met_user. Must the late hours heheh

SnakeSheet Over a year ago

Yes, that would be great if the empty ones showed up :)

Unihedron Over a year ago

@SnakeSheet Added - I also made an SQL Demo.

Collectives™ on Stack Overflow

MySQL how to get number of values from a user?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related