CODEWITHSUNDEEP
sql-serverstored-procedures
KyLim
KyLim

Reputation: 476

SQL insert #TEMP table

CREATE TABLE #TEMP (Date  NVARCHAR(256),Pending NVARCHAR(256),Complete NVARCHAR(256) ,Total  NVARCHAR(256));
INSERT INTO #TEMP (Date,Pending,Total) 
SELECT DISTINCT(DATEE),'','' from E_R_MainT
GROUP BY DATEE

UPDATE #TEMP
SET Pending=(SELECT COUNT(R_ID) FROM E_R_MainT WHERE STATUS !='S_2' GROUP BY (CONVERT(char(10), HOD_DT ,126)) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY DATEE)

UPDATE #TEMP
SET Complete=(SELECT COUNT(R_ID) FROM E_R_MainT WHERE STATUS !='S_1' GROUP BY DATEE )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY DATEE )

UPDATE #TEMP
SET Total=(SELECT COUNT(R_ID) FROM E_R_MainT  GROUP BY (DATEE) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY  DATEE
SELECT * FROM #TEMP

when execute my query i get this error :

"Subquery returned more than 1 value. This is not permitted "

below is my table source: enter image description here

i am expecting this output: enter image description here

Upvotes: 0

Views: 146

Answers (3)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

This example code from you:

UPDATE #TEMP
SET Total=(SELECT COUNT(R_ID) FROM E_R_MainT  GROUP BY (DATEE) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY  DATEE
SELECT * FROM #TEMP

The content of this

SELECT COUNT(R_ID) FROM E_R_MainT  GROUP BY (DATEE) )
WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY  DATEE

is not return only one, I thought it returns more than one value, Check all of your query above that look like that. Im sure it is the cause of giving you that error.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Some confusing naming going on here such that I suspect that several names that appear distinct in parts of your query are in fact meant to name the same column(s).

There shouldn't be a need for a temp table here - just use suitable aggregates:

SELECT
    DATEE,
    SUM(CASE WHEN Status = 's_1' THEN 1 ELSE 0 END) as Pending,
    SUM(CASE WHEN Status = 's_2' THEN 1 ELSE 0 END) as Complete,
    COUNT(*)
FROM E_R_MainT
GROUP BY DATEE

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

This part is causing issues for you..

WHERE #TEMP.Date =  (SELECT DISTINCT(DATEE) from E_R_MainT
GROUP BY DATEE)

change to..below ,no need of distinct and group by 

WHERE #TEMP.Date  in  (SELECT (DATEE) from E_R_MainT

Upvotes: 0

Related Questions