How to get count of values in stored procedure

C# Corner

Sivakumar

10y935

Hi,

I want to get count of Job status like count of open,count of closed jobs and count of processing jobs

This is my procedure :

USE [SmartJob

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[GetJobStatus]

@CompanyID INT

BEGIN

SELECT [StatusID]

,M.Value 'Status' FROM Jobs J

JOIN Master M ON M.MasterID=J.StatusID

WHERE CompanyID=@CompanyID;

END

Please help me

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Devendra Kumar10y ago
SELECT Status,( COUNT(1)) FROM Jobs JOIN Master ON Jobs.StatusID = Master.StatusID
group by Jobs.Status
0
K P Singh Chundawat10y ago
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.
0

Chandu Kumawat10y ago

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

-1

Ranjit Powar10y ago
You can solve this using group by clause as below

SELECT M.Value,Count( [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.

-1