sql server count() function?

Question

I have this Sql Query to show me how many task under Project but the problem it doesn't display Projects with 0 task can someone help me?

SELECT Projects.projectName AS [Project Name],
    COUNT(Projects_tasks.taskID) AS #tasks,
    Projects.projectID AS [Project ID]

FROM Projects

INNER JOIN Projects_tasks
ON Projects.projectID = Projects_tasks.projectID 

GROUP BY Projects.projectName,
    Projects_tasks.taskID,
    Projects.projectID

Gonzalo.- · Accepted Answer · 2012-08-22 14:28:28Z

1

Use Left Join

SELECT     Projects.projectName AS [Project Name],
                  ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]

FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 

GROUP BY Projects.projectName,Projects.projectID

also remove the column that you are counting in the group by

edited Aug 22, 2012 at 14:28

answered Aug 22, 2012 at 14:14

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DCNYAM · Accepted Answer · 2012-08-22 14:14:37Z

1

Use LEFT join, ISNULL(), and do not group by the column you are counting.

SELECT     Projects.projectName AS [Project Name],
                  ISNULL(COUNT(Projects_tasks.taskID), 0) AS #tasks, Projects.projectID AS [Project ID]

FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 

GROUP BY Projects.projectName,Projects.projectID

answered Aug 22, 2012 at 14:14

DCNYAM

12.2k8 gold badges57 silver badges71 bronze badges

Comments

Razvan · Accepted Answer · 2012-08-22 14:14:01Z

0

You should use a LEFT JOIN to make sure the projects with 0 tasks are also included

SELECT     Projects.projectName AS [Project Name],
           ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]
FROM       Projects LEFT JOIN
                  Projects_tasks ON Projects.projectID = Projects_tasks.projectID 

GROUP BY Projects.projectName,Projects_tasks.taskID,Projects.projectID

answered Aug 22, 2012 at 14:14

Razvan

10.2k6 gold badges41 silver badges51 bronze badges

Comments

FJT · Accepted Answer · 2012-08-22 14:14:48Z

0

Try changing INNER JOIN to LEFT JOIN

You're not seeing 0, because there is no task to join to.

answered Aug 22, 2012 at 14:14

FJT

2,0831 gold badge16 silver badges14 bronze badges

Comments

John Woo · Accepted Answer · 2012-08-22 14:20:58Z

0

Use LEFT JOIN. And also COALESCE to convert null values. COALESCE must be inside the COUNT function since the column Projects_tasks.taskID can be NULL.

SELECT Projects.projectName AS [Project Name],
       COUNT(COALESCE(Projects_tasks.taskID, 0)) AS #tasks,
       Projects.projectID AS [Project ID]
FROM   Projects
         LEFT JOIN Projects_tasks
            ON Projects.projectID = Projects_tasks.projectID 
GROUP BY Projects.projectName,
    Projects_tasks.taskID,
    Projects.projectID

edited Aug 22, 2012 at 14:20

answered Aug 22, 2012 at 14:15

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Dumitrescu Bogdan · Accepted Answer · 2012-08-22 14:21:14Z

0

I doubt that your query will ever return more then 1 task as number of tasks. The Projects_tasks.taskID should not be in the group clause. And for your problem, you have to put LEFT JOIN instead of inner join and it will work.

SELECT Projects.projectName AS [Project Name],
    sum(case when Projects_tasks.taskID is null then 0 else 1 end) AS #tasks,
    Projects.projectID AS [Project ID]

FROM Projects  

LEFT JOIN Projects_tasks
ON Projects.projectID = Projects_tasks.projectID 

GROUP BY Projects.projectName,
    Projects.projectID

edited Aug 22, 2012 at 14:21

answered Aug 22, 2012 at 14:15

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

Comments

Nicholas · Accepted Answer · 2012-08-22 14:53:16Z

0

Try by changing INNER JOIN into LEFT JOIN

SELECT     Projects.projectName AS [Project Name],
       ISNULL(COUNT(Projects_tasks.taskID),0) AS #tasks, Projects.projectID AS [Project ID]

FROM Projects LEFT JOIN Projects_tasks ON Projects.projectID = Projects_tasks.projectID

GROUP BY Projects.projectName,Projects_tasks.taskID,Projects.projectID

answered Aug 22, 2012 at 14:53

Nicholas

2471 silver badge2 bronze badges

Collectives™ on Stack Overflow

sql server count() function?

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related