what is the best SQL for this scenario
i have a table that has the following columns:
Team
Region
Person
Name
and i want a SQL report to produce one row for each team / region (groupby Team, Region) combination and also show me a column of total count of people that exist for that Team and Region. One sugges开发者_JS百科tion was to have a column with a value of 1 to them do a sum on that columns but there must be a simpler solution. How can i do a count on people in this output?
select
Team,
Region,
count(distinct Person)
from mytable
group by 1,2
select Team,
Region,
count(*) as PersonCount
from YourTable
group by Team, Region
SELECT Team, Region, COUNT(*)
FROM Table
GROUP BY Team, Region
Should work just fine, unless i'm missing something.
select Team, Region, Count(Team) as PeopleCount
from YourTable
group by Team, Region
should do it. Note that some may suggest Count(all). Don't get used to using Count(all) IMO is a bad practice. If your have an index column in your table always use that in Count() wherever applicable. Then your query will be much faster since theres a possibility that depending on how the indexes are created, your query may end up using the index only and not touch the table.
精彩评论