CODEWITHSUNDEEP
sql
AfterImage
AfterImage

Reputation: 115

Sql Distinct Count of resulting table with no conditionals

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?

Upvotes: 1

Views: 268

Answers (5)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

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

Upvotes: 1

pj.
pj.

Reputation: 479

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);

Upvotes: 0

codingbadger
codingbadger

Reputation: 44032

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

Upvotes: 0

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59533

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> ).

Upvotes: 0

z-boss
z-boss

Reputation: 17618

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

Upvotes: 0

Related Questions