How to get count of values in stored procedure

Question

Hi I want to get count of Job status like count of opencount of closed jobs and count of processing jobs This is my procedure : USE [SmartJob GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[GetJobStatus] @CompanyID INT AS BEGIN SELECT [StatusID] M.Value 'Status' FROM Jobs J JOIN Master M ON M.MasterID=J.StatusID WHERE CompanyID=@CompanyID; END Please help me

Devendra Kumar · Answer

SELECT Status( COUNT(1)) FROM Jobs JOIN Master ON Jobs.StatusID = Master.StatusID group by Jobs.Status

K P Singh Chundawat · Answer

SELECT SUM(IF(Status= ' open ' 1 0)) AS open  SUM(IF(Status= ' count ' 1 0)) AS count  SUM(IF(Status= ' closed ' 1 0)) AS closed FROM Jobs. It might be helpful for you.

Chandu Kumawat · Answer

CREATE TABLE # temp ( ID INT  NAME VARCHAR ( 100 ) ... ) INSERT INTO # temp Exec dbo . MyStoredProc SELECT COUNT (*) FROM # temp SELECT ID  NAME FROM # temp WHERE ID = 10 DROP TABLE # temp

Ranjit Powar · Answer

You can solve this using group by clause as below SELECT M.ValueCount( [StatusID]) FROM Jobs J JOIN Master M ON M.MasterID=J.StatusID WHERE CompanyID=@CompanyID GROUP BY M.Value If it helps you select it as correct answer.