How to find how many times X appears

Learning SQL using SSMS and I have been given a database and a 32 questions. I am working my way through the questions and got suck on a question of “How many people live in each city?” I know how to get how many times a singular entry is but not multiple.

Table:

create table client
(
custref     smallint primary key,
firstname   Varchar(20),
lastname    Varchar(20),    
city        Varchar(20),
balance     decimal(10,2),
creditlimit     integer,
repref      smallint,
foreign key (repref) references rep (repref)
);
GO

Insert’s:

insert into client values(124, 'Sally', 'Adams', 'Dunedin', 418.75, 500, 3);
insert into client values(256, 'Ann', 'Samuals', 'Palmerston', 10.75, 800, 11);
insert into client values(311, 'Don', 'Charles', 'Christchurch', 200.10, 300, 6);
insert into client values(315, 'Tom', 'Daniels', 'Christchurch', 320.75, 300, 3);
insert into client values(405, 'Al', 'Williams', 'Christchurch', 201.75, 800, 11);
insert into client values(412, 'Sandra', 'Adams', 'Palmerston', 908.75, 1000, 6);
insert into client values(522, 'Mary', 'Nelson', 'Christchurch', 49.50, 800, 3);
insert into client values(567, 'Jo', 'Baker', 'Dunedin', 201.20, 300, 3);
insert into client values(587, 'Judy', 'Roberts', 'Dunedin', 57.75, 500, 6);
insert into client values(622, 'Dan', 'Martin', 'Dunedin', 575.50, 500, 6);
insert into client values(635, 'Peter', 'Paulls', 'Dunedin', 100.56, 500, 3);
insert into client values(640, 'Margaret', 'Jones', 'Dunedin', 114.56, 800, 3);
insert into client values(649, 'Robert', 'McKenzie', 'Palmerston', 200, 500, 11);
insert into client values(675, 'Greg', 'Johnson', 'Christchurch', 15, 300, 6);
insert into client values(680, 'Peter', 'Yu', 'Dunedin', 10, 500, 6);

Answer

You can concatenate both the First name and Last name of the peoples to find the total Count

SELECT City,COUNT(CONCAT(Firstname,' ',Lastname)) AS Total_People
FROM CLIENT
GROUP BY City

Advertisement

Answer