Sql Distinct Count of resulting table with no conditionals

Question

Hello everyone,

I want to count the number of accounts from the resulting table generated from this code. This way, I know how many people liked blue at one time.

Select Distinct PEOPLE.FullName, PEOPLE.FavColor From PEOPLE
Where FavColor='Blue'

Lets say this is a history accounting of what people said their favorite color when they were asked so there may be multiple records of the same full name if asked again at a much later time; hence the distinct.

The code I used may not be reusable in your answer so feel free to use what you think can work. I am sure I found a possible solution to my problem using declare and if statements but I lost that page... so I am left with no solution. However, I think there is a way to do it without using conditionals which is what I am asking and rather have. Thanks.

Edit: My question is: From the code above, is there a way to count the number of accounts in the resulting table?

Answer 1

I think this may be what you want.

Select Count(*) as 'NumberOfPeople' From ( Select Distinct PEOPLE.FullName, PEOPLE.FavColor From PEOPLE Where FavColor='Blue' )a

Answer 2

If I understand what you are asking correctly (how many people liked blue at one time?), try this:

select count(*)
from PEOPLE
where FavColor = 'Blue'
group by FullName

If your question is in fact, how can I count the results of any select query?, you can do this:

Suppose your original query is:

select MyColumn
from MyTable
where MyOtherColumn = 26

You can wrap it in another query to get the count

select count(*)
from (
    select MyColumn
    from MyTable
    where MyOtherColumn = 26
) a

Answer 3

Select Count (Distinct PEOPLE.FullName)  
From PEOPLE  
Where FavColor='Blue'

Answer 4

Are you saying that you want to generate the count with no WHERE clause?

How about this?

SELECT
    count(*)
FROM
    people
    INNER JOIN (SELECT FavColor = 'Blue') col ON col.FavColor = people.FavColor

Edit: OK, I see what you want now.

You just need to wrap your query in SELECT count(*) FROM ( <your-query-goes-here> ).

Answer 5

If the same person has multiple answers, that is, their favourite colour has changed over time - resulting in several colours, some repeating, for the same person (aka "account") then to find the number of accounts where the favourite colour is / was blue (oracle):

select count(*) from (select FULLNAME from PEOPLE where FAVCOLOR = 'BLUE' group by FULLNAME);