0

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

2 Answers2

2

Aggregate functions like count() are applied to each group if you define a group:

select city, count(*)
from client
group by city
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • @koopa . . . Assuming that you want to count clients, this is the better approach. I also note that it appeared 12 minutes earlier. – Gordon Linoff May 11 '20 at 12:09
0

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
Thiyagu
  • 1,260
  • 1
  • 5
  • 14
  • 1
    why do you need to `concat()` the name in order to find the `count()` ? – Squirrel May 11 '20 at 05:22
  • Performance should be slightly better than mentioning the Column name rather than the * -@Squirrel – Thiyagu May 11 '20 at 05:32
  • I am not aware of that there is performance difference for this in `SQL Server`. Care to share some reference on this ? – Squirrel May 11 '20 at 05:51
  • COUNT(*) counts all rows ; COUNT(column) counts non-NULLs only -- @Squirrel Reference https://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct – Thiyagu May 11 '20 at 06:00
  • @Thiyagu . . . You have that backwards. `count(*)` should be faster than any expression that refers to columns. In fact, in some cases, it could be much, much faster (if the rows are wide and an index is available for the count). – Gordon Linoff May 11 '20 at 12:10
  • But, In this Case Count(*) Counts the Rows both "null" and "Not null" Client Names in the Count, So that i am Concatenating the columns in the Above Query. -- @Gordon Linoff – Thiyagu May 11 '20 at 12:19
  • @Thiyagu . . . There are no examples of `NULL` values in the data and no reason to think they are there. You could also filter them out using a `WHERE` clause. – Gordon Linoff May 11 '20 at 12:45