SQL Counting up rows resulting from a sub query

Question

SELECT Count the rows that are in this query --> (SELECT Family FROM BIRD GROUP BY Family) FROM BIRD

Every time I just try and count that sub query I get an error saying that there is more than one resulting value. I'm not sure how to count up the rows resulting from a sub query, any ideas?

Why not just select count(*) from (select family form bird group by family)? — squiguy
– squiguy, Commented Mar 16, 2014 at 7:18
Thanks heaps, I'm new to sql and wasnt aware that you could "FROM" to something like that. If you put it as an answer I'll accept it. — Sev
– Sev, Commented Mar 16, 2014 at 7:22
Others already did. Don't worry, SQL becomes an art and you will get it. — squiguy
– squiguy, Commented Mar 16, 2014 at 7:23

Mureinik · Accepted Answer · 2014-03-16 07:22:49Z

2

You could put this sub-query in the from clause:

SELECT COUNT(*) 
FROM   (SELECT   family
        FROM     bird
        GROUP BY family) t

But if you're just trying to get the number of different bird families you don't really need a subquery:

SELECT COUNT (DISTINCT family)
FROM   bird

answered Mar 16, 2014 at 7:22

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

Raging Bull · Accepted Answer · 2014-03-16 07:25:53Z

1

Try this:

SELECT Count(*) as FamilyCount 
FROM (SELECT Family FROM BIRD
      GROUP BY Family) Families

Count() returns the number of items in a group. Read more here.

edited Mar 16, 2014 at 7:25

answered Mar 16, 2014 at 7:20

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Comments

uma · Accepted Answer · 2014-03-16 07:41:14Z

0

try this for getting count, there is no need to sub-query for counting

select count( distinct family) from bird;

answered Mar 16, 2014 at 7:41

uma

2,97229 silver badges20 bronze badges

Comments

Choudhury Saadmaan Mahmid · Accepted Answer · 2014-03-16 08:27:46Z

0

Assuming you just want a count of the rows, let's have a go at this:

SELECT COUNT(*) FROM (SELECT Family FROM BIRD GROUP BY Family)

answered Mar 16, 2014 at 8:27

Choudhury Saadmaan Mahmid

1,3063 gold badges29 silver badges56 bronze badges

Collectives™ on Stack Overflow

SQL Counting up rows resulting from a sub query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related