Condition Inside Count Function Using Case In Sql Server

Question

I have one table having two columns named OrderId and OrderType

OrderId OrderType
------- ---------
1       Drawer
2       Pickup
3       Delivery
4       Delivery
5       Drawer

In my stored procedure I want to pass the argument of OrderType which would return a result only on matching OrderType Column data to the argument i.e. if I pass the argument 'Drawer' then it should return count of OrderType column where OrderType column data is 'Drawer' and so on.

How can i do that using Count?

Currently I am doing:

select @tempvar1=case @tempvar
when 'Drawer'
Then Count(CASE_TABLE.OrderType) --What Should i put here to get desired result?
end
select @tempvar1

Instead of counting the whole column I want only the count matching OrderType.

Are you asking where to use a WHERE clause to get your specific data? If so use a WHERE clause after your second select like you would in a normal query. — Mic1780
– Mic1780, Commented Feb 26, 2014 at 15:35
No i am asking What should i put in count to get the desired matching data instead of counting whole column rows — Muhammad Ali
– Muhammad Ali, Commented Feb 26, 2014 at 15:36
See @MattJohnson answer below. It look like it does what you want with Count() — Mic1780
– Mic1780, Commented Feb 26, 2014 at 15:39

brazilianldsjaguar · Accepted Answer · 2014-02-26 15:35:48Z

1

I'd so something like this:

DECLARE @argument VARCHAR(9) = 'Drawer'

SELECT
  OrderType,
  COUNT(OrderType) AS [Count]
FROM
  [Order]
WHERE
  OrderType = @argument
GROUP BY
  OrderType

Results

OrderType Count
--------- -----
Drawer    2

You can test it out at SqlFiddle here.

answered Feb 26, 2014 at 15:35

brazilianldsjaguar

1,4491 gold badge21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brazilianldsjaguar Over a year ago

This is assuming SQL Server 2008+ (the DECLARE statement)

sunk · Accepted Answer · 2014-02-26 15:59:10Z

0

Invoke SP:-

Exec GetOrderType 'Drawer'

SP:-

CREATE PROCEDURE GetOrderType
     @OrderType nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        COUNT(*) 
    FROM
        your_table_name
    WHERE
        OrderType = @OrderType
    GROUP BY
        OrderType

END
GO

answered Feb 26, 2014 at 15:59

sunk

3201 gold badge3 silver badges9 bronze badges

Collectives™ on Stack Overflow

Condition Inside Count Function Using Case In Sql Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related