SELECT and COUNT data in a specific range

user_6720566 0
Viewed 33

I would like to check all records for a certain range (1-10) and output the quantity. If there is no record with the value in the database, 0 should also be output.

Example database:

CREATE TABLE exampledata (
ID int,
port int,
name varchar(255));

Example data:

INSERT INTO exampledata
VALUES (1, 1, 'a'), (2, 1, 'b'), (3, 2, 'c'), (4, 2, 'd'), (5, 3, 'e'), (6, 4, 'f'), (7, 8, 'f');

My example query would be:

SELECT
port,
count(port) as amount
FROM exampledata
GROUP BY port

Which would result in:

port amount
1 2
2 2
3 1
4 1
8 1

But I need it to look like that:

port amount
1 2
2 2
3 1
4 1
5 0
6 0
7 0
8 1
9 0
10 0

I have thought about a join with a database that has the values 1-10 but this does not seem efficient. Several attempts with different case and if structures were all unsuccessful...

I have prepared the data in a db<>fiddle.

range count sql-server sql
1 Answers

This "simple" answer here would be to use an inline tally. As you just want the values 1-10, this can be achieved with a simple VALUES table construct:

SELECT V.I AS Port,
       COUNT(ed.ID) AS Amount
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I)
     LEFT JOIN dbo.exampledata ed ON V.I = ed.port
GROUP BY V.I;

Presumably, however, you actually have a table of ports, and so what you should be doing is LEFT JOINing from that:

SELECT P.PortID AS Port,
       COUNT(ed.ID) AS Amount
FROM dbo.Port P
     LEFT JOIN dbo.exampledata ed ON P.PortID = ed.port
WHERE P.PortID BETWEEN 1 AND 10
GROUP BY V.I;

If you don't have a table of ports (why don't you?), and you need to parametrise the values, I suggest using a actual Tally Table or Tally function; a search of these will give you a wealth of resources on how to create these.

user_20299830
Related