CODEWITHSUNDEEP
sql-server
Jamie Taylor
Jamie Taylor

Reputation: 3530

SQL Query need to show blank records

I have a query where i'm selecting 50 ids from the DB and grouping them by the id.

The only problem is that only 34 exists in the DB so it only shows 34 results.

My desired result is to show 50 results with the ones that don't exists in.

Is this possible?

Thanks

My query

SELECT no, count(no) as visits FROM DB 
WHERE (no = '1' or no = '2'  or no = '3' or no = '4' or no = '5')
GROUP BY no

This example will only give me 3 records even though i'm selecting 5 records

Upvotes: 1

Views: 1597

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You could add a tally table using WITH and LEFT JOIN your original table with the tally table.

;with q (no) AS (
  SELECT 1
  UNION ALL 
  SELECT no + 1
  FROM   q
  WHERE  no < 50
)
SELECT  q.no
        , CASE WHEN db.no IS NOT NULL 
               THEN COUNT(q.no)
               ELSE 0 
          END AS Visits
FROM    q
        LEFT JOIN db ON db.no = q.no
GROUP BY
        no

Upvotes: 1

RollingBoy
RollingBoy

Reputation: 2817


with noTable
as 
( select 1 as no
union
  select 2
union 
  select 3
union 
  select 4
union 
  select 5
)

select noTable.no,count(*) 
  from noTable inner join DB 
  on noTable.no = DB.no group by noTable.no
union
select noTable.no,0
  from noTable left join DB
  on noTable.no = DB.no
  where DB.no is null;

Might not be elegant if there're 50 id...

Upvotes: 2

Related Questions