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. 开发者_StackOverflowThis 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?
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
Select Count (Distinct PEOPLE.FullName)
From PEOPLE
Where FavColor='Blue'
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> )
.
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
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);
精彩评论