Styjun

Inspiration for failed idea?

How can I observe Sgr A* with itelescope.net

What is the sound/audio equivalent of "unsightly"?

Board Chinese train at a different station (on-route)

What does "-1" represent in the value range for unsigned int and signed int?

How to handle inventory and story of a player leaving

Can I lend a small amount of my own money to a bank at the federal funds rate?

Is the Amazon rainforest the "world's lungs"?

Is it recommended to point out a professor's mistake during their lecture?

Why does the weaker C–H bond have a higher wavenumber than the C=O bond?

Which polygons can be turned inside out by a smooth deformation?

Why did the population of Bhutan drop by 70% between 2007 and 2008?

Can two aircraft stay on the same runway at the same time?

Are spot colors limited and why CMYK mix is not treated same as spot color mix?

Unable to enroll in insurance now because of typo in email address

Did ancient peoples ever hide their treasure behind puzzles?

Why do IR remotes influence AM radios?

Why military weather satellites?

Generic method to call API functions with simple retrial on errors

Why did Lucius make a deal out of Buckbeak hurting Draco but not about Draco being turned into a ferret?

Why are JWST optics not enclosed like HST?

Moscow SVO airport, how to avoid scam taxis without pre-booking?

Old scifi book featuring krakens attacking humans

is "prohibition against," a double negative?

Count total number of rows where this.row is related with rows in another table

What's the best practice for primary keys in tables?Foreign key referring to primary keys across multiple tables?Do I need to create indexes on foreign keys?postgreSQL Duplicate rows counting on joinShould i add a non clustered index over a clustered index table, based on this where clause and join?counting rows in two related tablescount number of hierarchical childrens in sqlCount number of rows in a table for a studentAutomatically maintain a total count of children in the parent tableCount rows that are not in another table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I have two simple tables, parents and children. I am trying to count the number of parents who have at least one child.

create table People(
 id integer unique,
 name varchar(120),
 primary key (id)
);

create table children(
 id integer unique,
 name varchar(120),
 parentId integer,
 primary key(id),
 foreign key (parentId) references People(id)
);

This is the code I tried but it gives me the total number of children instead:

select count(*)
from (people p join children ch on ch.parentid = p.id)
having count(ch.id) > 0; 

sql postgresql

share|improve this question

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can only use HAVING clause when you use GROUP BY
  
  – Alagu Veerappan
  Mar 27 at 22:29
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Are you using MySQL or Postgres? These are different RDBMS, with vendor specificities. I removed the conflicting tags, please add back the relevant one.
  
  – GMB
  Mar 27 at 22:46
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @GMB i am using Postgres
  
  – Sachihiro Takamori
  Mar 27 at 22:50
  

  
  
  
  

add a comment | 

1

I have two simple tables, parents and children. I am trying to count the number of parents who have at least one child.

create table People(
 id integer unique,
 name varchar(120),
 primary key (id)
);

create table children(
 id integer unique,
 name varchar(120),
 parentId integer,
 primary key(id),
 foreign key (parentId) references People(id)
);

This is the code I tried but it gives me the total number of children instead:

select count(*)
from (people p join children ch on ch.parentid = p.id)
having count(ch.id) > 0; 

sql postgresql

share|improve this question

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can only use HAVING clause when you use GROUP BY
  
  – Alagu Veerappan
  Mar 27 at 22:29
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Are you using MySQL or Postgres? These are different RDBMS, with vendor specificities. I removed the conflicting tags, please add back the relevant one.
  
  – GMB
  Mar 27 at 22:46
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @GMB i am using Postgres
  
  – Sachihiro Takamori
  Mar 27 at 22:50
  

  
  
  
  

add a comment | 

I have two simple tables, parents and children. I am trying to count the number of parents who have at least one child.

create table People(
 id integer unique,
 name varchar(120),
 primary key (id)
);

create table children(
 id integer unique,
 name varchar(120),
 parentId integer,
 primary key(id),
 foreign key (parentId) references People(id)
);

This is the code I tried but it gives me the total number of children instead:

select count(*)
from (people p join children ch on ch.parentid = p.id)
having count(ch.id) > 0; 

sql postgresql

share|improve this question

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

create table People(
 id integer unique,
 name varchar(120),
 primary key (id)
);

create table children(
 id integer unique,
 name varchar(120),
 parentId integer,
 primary key(id),
 foreign key (parentId) references People(id)
);

select count(*)
from (people p join children ch on ch.parentid = p.id)
having count(ch.id) > 0; 

share|improve this question

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

share|improve this question

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

edited Mar 27 at 22:50

GMB

22.3k66 gold badges1111 silver badges2828 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

asked Mar 27 at 22:18

Sachihiro TakamoriSachihiro Takamori

31144 silver badges1414 bronze badges

6 Answers
6

active

oldest

votes

1

I am trying to count the number of parents who have at least one children.

This should be as simple as:

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

Using EXISTS is usually the most efficient way to check that something, well, exists.

share|improve this answer

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

add a comment | 

1

You're close. You just need to make the check for children on a per-parent basis:

SELECT COUNT(*) AS parents_with_children
FROM (SELECT p.name, COUNT(c.id) AS num_children
 FROM people p
 JOIN children c ON c.parentid = p.id
 GROUP BY p.name
 HAVING COUNT(c.id) > 0) p

Demo on dbfiddle

share|improve this answer

edited Mar 27 at 22:59

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get an error for this, column "num_children" does not exist, tbh i had similar solution myself and got similar error, and i could not debug it. Sorry for my noob questions.
  
  – Sachihiro Takamori
  Mar 27 at 22:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori did you use a HAVING or a WHERE clause? You can't use an alias in a WHERE clause.
  
  – Nick
  Mar 27 at 22:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori I've corrected a minor error in the query, and added a demo of it working.
  
  – Nick
  Mar 27 at 22:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i corrected too, adding the alias in the end but i still get the error, does it have to do because I am running postgres?
  
  – Sachihiro Takamori
  Mar 27 at 22:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori ah... when I answered the question it was tagged MySQL... I've updated the query so it will work on PostgreSQL
  
  – Nick
  Mar 27 at 22:59
  

  
  
  
  

add a comment | 

0

SELECT COUNT(*),p.*
FROM People p JOIN children c ON c.parnetId=p.id
WHERE NOT c.parnetId IS NULL 
GROUP BY (p.id)

(no need for having since it only joins existing children anyways)

share|improve this answer

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

add a comment | 

0

select count(p.*)
from people p inner join children ch
 on ch.parentid = p.id 

share|improve this answer

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Would you add some explanation, please ?
  
  – Vega
  Mar 27 at 22:47
  

  
  
  
  

add a comment | 

0

You could try something like this,

SELECT COUNT(DISTINCT children.parentid)
FROM People 
INNER JOIN children 
ON children.parentid = people.id;

share|improve this answer

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

add a comment | 

0

With EXISTS:

select count(distinct p.id) counter from people p
where exists (
 select 1 from children
 where parentid = p.id
)

or even better:

select count(distinct parentid) counter 
from children 

because all the info you need is in the table children, so just count the distinct values in column parentid

share|improve this answer

edited Mar 27 at 22:52

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What does select 1 from children means?
  
  – Sachihiro Takamori
  Mar 27 at 22:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What matters here is EXISTS. If there is at least 1 row returned then the condition is true. Select 1 could be select 0 or select * it does not matter as long as 1 row is returned.
  
  – forpas
  Mar 27 at 22:39
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Read more here: dev.mysql.com/doc/refman/8.0/en/…
  
  – forpas
  Mar 27 at 22:40
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55387340%2fcount-total-number-of-rows-where-this-row-is-related-with-rows-in-another-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

I am trying to count the number of parents who have at least one children.

This should be as simple as:

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

Using EXISTS is usually the most efficient way to check that something, well, exists.

share|improve this answer

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

add a comment | 

1

I am trying to count the number of parents who have at least one children.

This should be as simple as:

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

Using EXISTS is usually the most efficient way to check that something, well, exists.

share|improve this answer

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

add a comment | 

I am trying to count the number of parents who have at least one children.

This should be as simple as:

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

Using EXISTS is usually the most efficient way to check that something, well, exists.

share|improve this answer

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

share|improve this answer

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

answered Mar 27 at 22:33

GMBGMB

22.3k66 gold badges1111 silver badges2828 bronze badges

1

You're close. You just need to make the check for children on a per-parent basis:

SELECT COUNT(*) AS parents_with_children
FROM (SELECT p.name, COUNT(c.id) AS num_children
 FROM people p
 JOIN children c ON c.parentid = p.id
 GROUP BY p.name
 HAVING COUNT(c.id) > 0) p

Demo on dbfiddle

share|improve this answer

edited Mar 27 at 22:59

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get an error for this, column "num_children" does not exist, tbh i had similar solution myself and got similar error, and i could not debug it. Sorry for my noob questions.
  
  – Sachihiro Takamori
  Mar 27 at 22:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori did you use a HAVING or a WHERE clause? You can't use an alias in a WHERE clause.
  
  – Nick
  Mar 27 at 22:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori I've corrected a minor error in the query, and added a demo of it working.
  
  – Nick
  Mar 27 at 22:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i corrected too, adding the alias in the end but i still get the error, does it have to do because I am running postgres?
  
  – Sachihiro Takamori
  Mar 27 at 22:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori ah... when I answered the question it was tagged MySQL... I've updated the query so it will work on PostgreSQL
  
  – Nick
  Mar 27 at 22:59
  

  
  
  
  

add a comment | 

1

You're close. You just need to make the check for children on a per-parent basis:

SELECT COUNT(*) AS parents_with_children
FROM (SELECT p.name, COUNT(c.id) AS num_children
 FROM people p
 JOIN children c ON c.parentid = p.id
 GROUP BY p.name
 HAVING COUNT(c.id) > 0) p

Demo on dbfiddle

share|improve this answer

edited Mar 27 at 22:59

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I get an error for this, column "num_children" does not exist, tbh i had similar solution myself and got similar error, and i could not debug it. Sorry for my noob questions.
  
  – Sachihiro Takamori
  Mar 27 at 22:46
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori did you use a HAVING or a WHERE clause? You can't use an alias in a WHERE clause.
  
  – Nick
  Mar 27 at 22:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori I've corrected a minor error in the query, and added a demo of it working.
  
  – Nick
  Mar 27 at 22:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i corrected too, adding the alias in the end but i still get the error, does it have to do because I am running postgres?
  
  – Sachihiro Takamori
  Mar 27 at 22:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SachihiroTakamori ah... when I answered the question it was tagged MySQL... I've updated the query so it will work on PostgreSQL
  
  – Nick
  Mar 27 at 22:59
  

  
  
  
  

add a comment | 

You're close. You just need to make the check for children on a per-parent basis:

SELECT COUNT(*) AS parents_with_children
FROM (SELECT p.name, COUNT(c.id) AS num_children
 FROM people p
 JOIN children c ON c.parentid = p.id
 GROUP BY p.name
 HAVING COUNT(c.id) > 0) p

Demo on dbfiddle

share|improve this answer

edited Mar 27 at 22:59

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

SELECT COUNT(*) AS parents_with_children
FROM (SELECT p.name, COUNT(c.id) AS num_children
 FROM people p
 JOIN children c ON c.parentid = p.id
 GROUP BY p.name
 HAVING COUNT(c.id) > 0) p

share|improve this answer

edited Mar 27 at 22:59

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

answered Mar 27 at 22:30

NickNick

51.6k1414 gold badges2525 silver badges4646 bronze badges

0

SELECT COUNT(*),p.*
FROM People p JOIN children c ON c.parnetId=p.id
WHERE NOT c.parnetId IS NULL 
GROUP BY (p.id)

(no need for having since it only joins existing children anyways)

share|improve this answer

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

add a comment | 

0

SELECT COUNT(*),p.*
FROM People p JOIN children c ON c.parnetId=p.id
WHERE NOT c.parnetId IS NULL 
GROUP BY (p.id)

(no need for having since it only joins existing children anyways)

share|improve this answer

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

add a comment | 

SELECT COUNT(*),p.*
FROM People p JOIN children c ON c.parnetId=p.id
WHERE NOT c.parnetId IS NULL 
GROUP BY (p.id)

(no need for having since it only joins existing children anyways)

share|improve this answer

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

SELECT COUNT(*),p.*
FROM People p JOIN children c ON c.parnetId=p.id
WHERE NOT c.parnetId IS NULL 
GROUP BY (p.id)

share|improve this answer

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

answered Mar 27 at 22:32

jonathan Heindljonathan Heindl

63422 silver badges1313 bronze badges

0

select count(p.*)
from people p inner join children ch
 on ch.parentid = p.id 

share|improve this answer

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Would you add some explanation, please ?
  
  – Vega
  Mar 27 at 22:47
  

  
  
  
  

add a comment | 

0

select count(p.*)
from people p inner join children ch
 on ch.parentid = p.id 

share|improve this answer

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Would you add some explanation, please ?
  
  – Vega
  Mar 27 at 22:47
  

  
  
  
  

add a comment | 

select count(p.*)
from people p inner join children ch
 on ch.parentid = p.id 

share|improve this answer

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

select count(p.*)
from people p inner join children ch
 on ch.parentid = p.id 

share|improve this answer

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

edited Mar 27 at 22:46

Vega

16.3k1313 gold badges4747 silver badges6969 bronze badges

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

answered Mar 27 at 22:29

Isaac Humberto De la Rosa MendIsaac Humberto De la Rosa Mend

1

0

You could try something like this,

SELECT COUNT(DISTINCT children.parentid)
FROM People 
INNER JOIN children 
ON children.parentid = people.id;

share|improve this answer

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

add a comment | 

0

You could try something like this,

SELECT COUNT(DISTINCT children.parentid)
FROM People 
INNER JOIN children 
ON children.parentid = people.id;

share|improve this answer

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

add a comment | 

You could try something like this,

SELECT COUNT(DISTINCT children.parentid)
FROM People 
INNER JOIN children 
ON children.parentid = people.id;

share|improve this answer

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

SELECT COUNT(DISTINCT children.parentid)
FROM People 
INNER JOIN children 
ON children.parentid = people.id;

share|improve this answer

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

answered Mar 27 at 22:48

Alagu VeerappanAlagu Veerappan

6888 bronze badges

0

With EXISTS:

select count(distinct p.id) counter from people p
where exists (
 select 1 from children
 where parentid = p.id
)

or even better:

select count(distinct parentid) counter 
from children 

because all the info you need is in the table children, so just count the distinct values in column parentid

share|improve this answer

edited Mar 27 at 22:52

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What does select 1 from children means?
  
  – Sachihiro Takamori
  Mar 27 at 22:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What matters here is EXISTS. If there is at least 1 row returned then the condition is true. Select 1 could be select 0 or select * it does not matter as long as 1 row is returned.
  
  – forpas
  Mar 27 at 22:39
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Read more here: dev.mysql.com/doc/refman/8.0/en/…
  
  – forpas
  Mar 27 at 22:40
  

  
  
  
  

add a comment | 

0

With EXISTS:

select count(distinct p.id) counter from people p
where exists (
 select 1 from children
 where parentid = p.id
)

or even better:

select count(distinct parentid) counter 
from children 

because all the info you need is in the table children, so just count the distinct values in column parentid

share|improve this answer

edited Mar 27 at 22:52

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What does select 1 from children means?
  
  – Sachihiro Takamori
  Mar 27 at 22:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What matters here is EXISTS. If there is at least 1 row returned then the condition is true. Select 1 could be select 0 or select * it does not matter as long as 1 row is returned.
  
  – forpas
  Mar 27 at 22:39
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Read more here: dev.mysql.com/doc/refman/8.0/en/…
  
  – forpas
  Mar 27 at 22:40
  

  
  
  
  

add a comment | 

With EXISTS:

select count(distinct p.id) counter from people p
where exists (
 select 1 from children
 where parentid = p.id
)

or even better:

select count(distinct parentid) counter 
from children 

because all the info you need is in the table children, so just count the distinct values in column parentid

share|improve this answer

edited Mar 27 at 22:52

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

select count(distinct p.id) counter from people p
where exists (
 select 1 from children
 where parentid = p.id
)

select count(distinct parentid) counter 
from children 

share|improve this answer

edited Mar 27 at 22:52

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges

answered Mar 27 at 22:32

forpasforpas

41.9k66 gold badges1212 silver badges3333 bronze badges