scenario: I have difficulty in building a single query that would display a report given the schema. Supposedly, the report to be displayed should follow a computation based on the multiplier. For instance: user, number of messages x multiplier
what is needed: mysql query that would display the expected query result.
* schema *
table: users
id | name
1 | cris
2 | dave
3 | jona
table: services
id | name
1 | standard
2 | premium
table: service_users
user_id | service_id | multiplier
1 | 1 | 1
1 | 2 | 2
2 | 1 | 1
2 | 2 | 2
3 | 1 | 1
3 | 2 | 2
table: messages
user_id | service_id | content
1 | 1 | howdy
1 | 1 | hey
1 | 1 | hello
1 | 2 | the quantity
1 | 2 | insignificant
2 | 1 | pill
2 | 1 | dock
2 | 2 | misty docks
3 | 1 | drive
3 | 2 | with style
3 | 2 | like a hawk
3 | 2 | earthling rise
query result should look 开发者_如何学Golike this
user_id | multiplier(1) | multiplier(2) | total
1 | 3 | 4 | 7
2 | 2 | 2 | 4
3 | 1 | 6 | 7
SELECT users.id,
COUNT( IF( service_users.multiplier = 1, 1, NULL ) AS m1,
COUNT( IF( service_users.multiplier = 2, 1, NULL ) AS m2
FROM users, messages, service_users
WHERE users.id = messages.user_id AND
messages.user_id = service_users.user_id
messages.service_id = service_users.service_id
GROUP BY users.id
精彩评论