PHP/MySQL - Counting items within a query

Question

I have the following results for my database table:

enter image description here

The Query:

SELECT 
service_titles.user_id, service_titles.slide_id, service_titles.name as title_name ,service_names.name as service_name 
FROM service_names 
INNER JOIN service_titles ON service_names.title_id = service_titles.id

So what needs to happen is:

If the user has 2 unique service titles, then the max number of service_names for that title will be 6
If the user has 1 service title, the the max number of service_names for that title will be 16

I will be using PHP for all of the coding, but I am wondering how I would go about this. I need a way to count how many unique service_titles there are for that user and slide, and then count how many service items there are for each title.

Thanks for any help!

Borealid · Accepted Answer · 2012-02-13 15:02:38Z

1

SELECT COUNT(DISTINCT service_titles.name)
FROM service_names
INNER JOIN service_titles ON service_names.title_id = service_titles.id
GROUP BY service_titles.user_id, service_titles.slide_id

That'll get you the number of distinct title_names for each user_id/slide_id combo.

SELECT COUNT(DISTINCT service_names.name)
FROM service_names
INNER JOIN service_titles ON service_names.title_id = service_titles.id
GROUP BY service_titles.user_id, service_titles.slide_id

... and that's the number of distinct service_names for same. If you want both in one query, you can put both COUNTs together, since you're using the same GROUP BY regardless.

answered Feb 13, 2012 at 15:02

Borealid

99.4k9 gold badges111 silver badges123 bronze badges

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

Comments

gibbotronic · Accepted Answer · 2012-02-13 15:01:13Z

0

You could use a CASE statement within your query to change the max number of service_names.

See MySQL CASE statement reference

To do this in the SQL itself would be quicker than evaluating it in PHP.

answered Feb 13, 2012 at 15:01

gibbotronic

818 bronze badges

Comments

Marco · Accepted Answer · 2012-02-13 15:02:16Z

0

To count how many distinct titles you can try:

SELECT user_id, COUNT(DISTINCT name)
FROM service_titles
GROUP BY user_id

answered Feb 13, 2012 at 15:02

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL - Counting items within a query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related