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.
CodePudding user response：
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.