default row in select

Question

Post reply

default row in select

rpatil22

Ten Centuries

Points: 1371
More actions
June 10, 2009 at 9:35 am

#155812

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 1

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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems