SQL Can I set up multiple counts in a query, then have each count be for a seperate select?

Question

Sorry I can't really explain it better than the title. Basically; I just would like to know if it would be possible to have a select with multiple 'count() as ___' in the initial select with each 'count()' counting rows for a different select.

Or would I be better off making a new table with one field, then doing a single select query with a count for each new field to be added on?

So lets say I want the following table:

NAME ---- # of cars ---- # of children ---- # of living relatives
Jon       2              2                  9
Jane      3              1                  10
Bob       1              1                  8

...and each of the # fields are taken from other selects in which I tie the person's name to each car/child/living relative they're in relation with. Could I do a single query with multiple count(*) in it? Or would it be better off to start a table with a query with a single count so I have something like this:

NAME ---- # of cars
Jon       2
Jane      3
Bob       1

and then keep adding fields to it with a few more single-count queries so it adds one new row at a time until it reaches the first table?

It's very difficult to understand what you are asking. Instead of trying to find the words to explain it, instead show us some example data and the result you are trying to achieve. — JNevill
– JNevill, Commented Jun 8, 2015 at 17:12
This still seems like the end data with no source data. Please post the schema for the tables your "SELECTs" are coming from. — Patrick Tucci
– Patrick Tucci, Commented Jun 8, 2015 at 17:25

krokodilko · Accepted Answer · 2015-06-08 17:54:31Z

1

If I understand correctly your question, you are looking for something like this query:

SELECT name,

       (SELECT count(*) FROM childrens
        WHERE childrens.parent_name = users.name ) As `# of children`,

       (SELECT count(*) FROM cars
        WHERE cars.owner_name = users.name ) As `# of cars`   

        -- etc.
        -- etc

FROM users

Demo: http://sqlfiddle.com/#!9/3473f/2

answered Jun 8, 2015 at 17:54

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

Comments

dnoeth · Accepted Answer · 2015-06-08 17:57:44Z

1

Assuming you need to join multiple tables (cars, children and relatives) you do each count separately and then join:

select p.Name,
   car.cnt,
   child.cnt,
   rel.cnt
from person as p
left join 
 ( select person_id, count(*)
   from cars
   group by person_id
 ) as car
on p.id = car.person_id
left join 
 ( select person_id, count(*)
   from children
   group by person_id
 ) as child
on p.id = child.person_id
left join 
 ( select person_id, count(*)
   from relatives
   group by person_id
 ) as rel
on p.id = rel.person_id

answered Jun 8, 2015 at 17:57

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Can I set up multiple counts in a query, then have each count be for a seperate select?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related