[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQL Column Count

Hey All,

Trying to write a simple query that will look in my table dbo.Profile count the total number of entries in 'City', then get a count for each city found.

Example:

Total = 140,000

Peoria = 150 entrie(s)
Ajo = 1 entrie(s)
Glendale = 9000 entrie(s)

Is this possible?
0
ValleyENT
Asked:
ValleyENT
  • 3
  • 2
  • 2
  • +1
1 Solution
 
chapmandewCommented:
select city, count(*) as numberofcities
from profile
group by city
0
 
chapmandewCommented:
quick change...won't really affect the output

select city, count(*) as numberofentries
from profile
group by city
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This will do
SELECT city, count(*) AS cnt
FROM dbo.Profile
GROUP BY city
WITH rollup 

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Auric1983Commented:
ValleyENT,

Assuming City is a column in the table Profile, you can execute the following query to get the results you are looking for

select City, count(*) from Profile group by City

if you really want the "Total" as well you could so something like this

select 'Total', count(*) from Profile
union all
select City,Count(*) from Profile group by City

0
 
ValleyENTAuthor Commented:
How would I include the corrosponding State if the column name was STATE?
SELECT City, COUNT(City) as "Total City(s)"
FROM dbo.PatientProfile
GROUP BY City ORDER By City

Open in new window

0
 
chapmandewCommented:
do you want it included w/ the city information or a new query just for state information?
0
 
ValleyENTAuthor Commented:
included please.
0
 
Auric1983Commented:
Select City, State, count(City) as "Total Cities" from dbo.PatientProfile group by City,State order by City, State
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now