June 10, 2009 at 9:35 am
Type table has three columns machine, material , store
select machine, material , count(*) as Counting from Type where Store ='East' group by Material
the above query returns zero no rows , then how to return defalt row
like
Machine Material Counting
A Diode 0
June 10, 2009 at 12:06 pm
You can use a CTE with a UNION ALL and an additional sort column:
DECLARE @t TABLE (Machine VARCHAR(10), Material VARCHAR(10), Store VARCHAR(10))
INSERT INTO @t
SELECT 'Notepad', 'Stone', 'East'
DECLARE @Store VARCHAR(10)
SELECT @Store = 'East'
; WITH
cte (Machine, Material, Counting, Sort) AS
(
SELECT
Machine,
Material,
COUNT(*),
1
FROM @t
WHERE Store = @Store
GROUP BY
Machine,
Material
UNION ALL
SELECT
'A',
'Diode',
0,
2
)
SELECT TOP(1)
Machine,
Material,
Counting
FROM cte
ORDER BY
Sort
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply