CODEWITHSUNDEEP
sqlsql-serverselect
Learner
Learner

Reputation: 1376

How to select all the records even if there is no data in sql

I have a query like

SELECT  'Education' as Purpose,ate_sex, Age_range, COUNT(*) AS Total
  FROM (
        SELECT dbo.ClientMain.ate_sex, dbo.ClientMain.ate_Id, 
          CASE
            WHEN ate_Age BETWEEN 15 AND 19 THEN '15-19'
            WHEN ate_Age BETWEEN 20 AND 24 THEN '20-24'
            WHEN ate_Age BETWEEN 25 AND 34 THEN '25-34'
            WHEN ate_Age BETWEEN 35 AND 44 THEN '35-44'
            WHEN ate_Age BETWEEN 45 AND 54 THEN '45-54' 
            WHEN ate_Age BETWEEN 55 AND 64 THEN '55-64'
            ELSE '80+' END AS Age_range
         FROM dbo.ClientMain 
         INNER JOIN dbo.ClientSub ON dbo.ClientMain.ate_Id = dbo.ClientSub.ate_Id
         WHERE (dbo.ClientSub.chk_Name = N'Assistance')
       ) AS t group by ate_sex,Age_range

Which returns the data as:

enter image description here

But I want my result as when there is no record with he age range in 15-19, it have to return zero. As Education Male 15-19 0 These are my tables enter image description here Can anybody please modify my query to get zeros for no records.

Upvotes: 1

Views: 3551

Answers (3)

Ofer Zelig
Ofer Zelig

Reputation: 17480

  • Use a LEFT JOIN instead of INNER JOIN

  • Use ISNULL(COUNT(*), 0) instead of COUNT(*). Alternatively, you can also use

    Total = CASE WHEN COUNT(*) IS NULL THEN 0 ELSE COUNT(*) END

Upvotes: 3

DB_learner
DB_learner

Reputation: 1026

If you doesnt have data with male, 15-19 in any table, yet you still want it in result with count as "0". Try building a static table with possible rows and do a cross join with your resultset.

Upvotes: 0

Chintan
Chintan

Reputation: 71

Try This,

case when COUNT(*) = 1 then 0 else COUNT(*) end as exp

Upvotes: 1

Related Questions