Link to home
Create AccountLog in
Avatar of aritasky
aritasky

asked on

Return (0) zero in SQL when no rows returned

This question is in relation to a previous question (https://www.experts-exchange.com/questions/28120239/SQL-Query-with-Case-Statement.html)  I asked recently except this time I need the results to return 0 when then are no results.  Is that possible? Any help appreciated.
Currently I get this:
NewStatus		Total
Received		47
In Progress		87
Shipped			464
Order Finalised		134598
Deleted Order		907

Open in new window

but need this:
NewStatus		Total
Received		47
In Progress		87
Ready For Despatch	0
Shipped			464
Partial Shipped		0
Order Finalised		134598
Deleted Order		907

Open in new window

Here is the query:
SELECT NewStatus, COUNT(*) as Total
FROM (SELECT 
CASE
	WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress' 
	WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
	WHEN order_status = 'C' THEN 'Ready For Despatch'
	WHEN order_status = 'S' THEN 'Shipped'
	WHEN order_status = 'P' THEN 'Partial Shipped'
	WHEN order_status = 'D' THEN 'Deleted Order'
	WHEN order_status = 'I' THEN 'Order Finalised'
	WHEN order_status = 'R' THEN 'Obsolete'
    ELSE 'Unknown' END AS NewStatus
   FROM ORDER_HEADER INNER JOIN (
               SELECT
                    order_id
                  , completed
                  , completed_on
                  , row_number() over (partition BY order_id ORDER BY completed_on DESC, completed) AS row_ref
               FROM ORDER_PROC_SEQUENCE
               ) as PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id AND PROC_SEQUENCE.row_ref = 1
   WHERE (customer_id like '%')
   GROUP BY ORDER_HEADER.order_id, ORDER_HEADER.order_status, PROC_SEQUENCE.completed) s
WHERE (NewStatus != 'Unknown' or NewStatus != 'Obsolete')
GROUP BY s.NewStatus
   ORDER BY
(CASE s.NewStatus
	WHEN 'Received' THEN 1
	WHEN 'In Progress' THEN 2
	WHEN 'Ready For Despatch' THEN 3
	WHEN 'Partial Shipped' THEN 4
	WHEN 'Shipped' THEN 5
	WHEN 'Order Finalised' THEN 6
	WHEN 'Deleted Order' THEN 7
	ELSE 100 END) ASC

Open in new window

Avatar of appari
appari
Flag of India image

try this
SELECT NewStatus, COUNT(*) as Total
FROM (SELECT 
CASE
	WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress' 
	WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
	WHEN order_status = 'C' THEN 'Ready For Despatch'
	WHEN order_status = 'S' THEN 'Shipped'
	WHEN order_status = 'P' THEN 'Partial Shipped'
	WHEN order_status = 'D' THEN 'Deleted Order'
	WHEN order_status = 'I' THEN 'Order Finalised'
	WHEN order_status = 'R' THEN 'Obsolete'
    ELSE 'Unknown' END AS NewStatus
   FROM ORDER_HEADER Left Outer JOIN (
               SELECT
                    order_id
                  , completed
                  , completed_on
                  , row_number() over (partition BY order_id ORDER BY completed_on DESC, completed) AS row_ref
               FROM ORDER_PROC_SEQUENCE
               ) as PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id AND PROC_SEQUENCE.row_ref = 1
   WHERE (customer_id like '%')
   GROUP BY ORDER_HEADER.order_id, ORDER_HEADER.order_status, PROC_SEQUENCE.completed) s
WHERE (NewStatus != 'Unknown' or NewStatus != 'Obsolete')
GROUP BY s.NewStatus
   ORDER BY
(CASE s.NewStatus
	WHEN 'Received' THEN 1
	WHEN 'In Progress' THEN 2
	WHEN 'Ready For Despatch' THEN 3
	WHEN 'Partial Shipped' THEN 4
	WHEN 'Shipped' THEN 5
	WHEN 'Order Finalised' THEN 6
	WHEN 'Deleted Order' THEN 7
	ELSE 100 END) ASC

Open in new window

SELECT NewStatus, isnull(COUNT(*),0) as Total
FROM (SELECT
CASE
      WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress'
      WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
      WHEN order_status = 'C' THEN 'Ready For Despatch'
      WHEN order_status = 'S' THEN 'Shipped'
      WHEN order_status = 'P' THEN 'Partial Shipped'
      WHEN order_status = 'D' THEN 'Deleted Order'
      WHEN order_status = 'I' THEN 'Order Finalised'
      WHEN order_status = 'R' THEN 'Obsolete'
    ELSE 'Unknown' END AS NewStatus
   FROM ORDER_HEADER INNER JOIN (
               SELECT
                    order_id
                  , completed
                  , completed_on
                  , row_number() over (partition BY order_id ORDER BY completed_on DESC, completed) AS row_ref
               FROM ORDER_PROC_SEQUENCE
               ) as PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id AND PROC_SEQUENCE.row_ref = 1
   WHERE (customer_id like '%')
   GROUP BY ORDER_HEADER.order_id, ORDER_HEADER.order_status, PROC_SEQUENCE.completed) s
WHERE (NewStatus != 'Unknown' or NewStatus != 'Obsolete')
GROUP BY s.NewStatus
   ORDER BY
(CASE s.NewStatus
      WHEN 'Received' THEN 1
      WHEN 'In Progress' THEN 2
      WHEN 'Ready For Despatch' THEN 3
      WHEN 'Partial Shipped' THEN 4
      WHEN 'Shipped' THEN 5
      WHEN 'Order Finalised' THEN 6
      WHEN 'Deleted Order' THEN 7
      ELSE 100 END) ASC
Avatar of aritasky
aritasky

ASKER

Sorry, neither of those queries helped, although with using the LEFT OUTER JOIN I got the following:
NewStatus		Total
Received		47
In Progress		87
Shipped			464
Order Finalised		134598
Deleted Order		907
Obsolete		4

Open in new window

try this
;With OrderSt as 
(Select 'In Progress' order_status
union all Select 'Received' 
union all Select 'Ready For Despatch' 
union all Select 'Shipped' 
union all Select 'Partial Shipped' 
union all Select 'Deleted Order' 
union all Select 'Order Finalised' 
 )

SELECT NewStatus, COUNT(*) as Total
FROM OrderSt Left Outer Join 
(Select * from (SELECT 
CASE
	WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress' 
	WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
	WHEN order_status = 'C' THEN 'Ready For Despatch'
	WHEN order_status = 'S' THEN 'Shipped'
	WHEN order_status = 'P' THEN 'Partial Shipped'
	WHEN order_status = 'D' THEN 'Deleted Order'
	WHEN order_status = 'I' THEN 'Order Finalised'
	WHEN order_status = 'R' THEN 'Obsolete'
    ELSE 'Unknown' END AS NewStatus
   FROM ORDER_HEADER INNER JOIN (
               SELECT
                    order_id
                  , completed
                  , completed_on
                  , row_number() over (partition BY order_id 
			ORDER BY completed_on DESC, completed) AS row_ref
               FROM ORDER_PROC_SEQUENCE
               ) as PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id 
		AND PROC_SEQUENCE.row_ref = 1
   WHERE (customer_id like '%')
   GROUP BY ORDER_HEADER.order_id, ORDER_HEADER.order_status, PROC_SEQUENCE.completed) s
WHERE (NewStatus != 'Unknown' or NewStatus != 'Obsolete')
GROUP BY s.NewStatus) S1
on OrderSt.order_status = S1.NewStatus
   ORDER BY 
(CASE OrderSt.order_status
	WHEN 'Received' THEN 1
	WHEN 'In Progress' THEN 2
	WHEN 'Ready For Despatch' THEN 3
	WHEN 'Partial Shipped' THEN 4
	WHEN 'Shipped' THEN 5
	WHEN 'Order Finalised' THEN 6
	WHEN 'Deleted Order' THEN 7
	ELSE 100 END) ASC

Open in new window

Error:
Column 'S1.NewStatus' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PortletPaul
some suggestions:
;WITH OrderSt
AS (

    SELECT  'Received' order_status , 1 sort_by
     UNION ALL
    SELECT  'In Progress' , 2
     UNION ALL
    SELECT  'Ready For Despatch' , 3
     UNION ALL
    SELECT  'Partial Shipped' , 4
     UNION ALL
    SELECT  'Shipped' , 5
     UNION ALL
    SELECT  'Order Finalised' , 6
     UNION ALL
    SELECT  'Deleted Order' , 7
    )
    
SELECT order_status
    , isnull(S1.Total, 0) Total
FROM OrderSt
LEFT JOIN (
    SELECT S.NewStatus
        , COUNT(*) AS Total
    FROM (
        SELECT CASE 
                WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress'
                WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
                WHEN order_status = 'C' THEN 'Ready For Despatch'
                WHEN order_status = 'S' THEN 'Shipped'
                WHEN order_status = 'P' THEN 'Partial Shipped'
                WHEN order_status = 'D' THEN 'Deleted Order'
                WHEN order_status = 'I' THEN 'Order Finalised'
                WHEN order_status = 'R' THEN 'Obsolete'
                ELSE 'Unknown'
                END AS NewStatus
        FROM ORDER_HEADER
        INNER JOIN (
                        SELECT order_id
                            , completed
                            , completed_on
                            , row_number() OVER (
                                PARTITION BY order_id ORDER BY completed_on DESC
                                    , completed
                                ) AS row_ref
                        FROM ORDER_PROC_SEQUENCE
                        
                        /* can you filter for customer here? & make use of the inner join */
                        --WHERE (customer_id LIKE '%')
                        
                        ) AS PROC_SEQUENCE ON PROC_SEQUENCE.order_id = ORDER_HEADER.order_id  AND PROC_SEQUENCE.row_ref = 1
                        
        WHERE (customer_id LIKE '%') /* only if you can't do it above */
        
        GROUP BY CASE 
                WHEN order_status = 'G' AND completed = 'Y' THEN 'In Progress'
                WHEN order_status = 'G' AND completed = 'N' THEN 'Received'
                WHEN order_status = 'C' THEN 'Ready For Despatch'
                WHEN order_status = 'S' THEN 'Shipped'
                WHEN order_status = 'P' THEN 'Partial Shipped'
                WHEN order_status = 'D' THEN 'Deleted Order'
                WHEN order_status = 'I' THEN 'Order Finalised'
                WHEN order_status = 'R' THEN 'Obsolete'
                ELSE 'Unknown'
                END
        ) s
    ) S1 ON OrderSt.order_status = S1.NewStatus
ORDER BY OrderSt.sort_by ASC

Open in new window

{+ edit, sorry} thought of something else
appari: It seems I get the required results using your suggestion.

Paul: Your suggestion gave the following errors:

Msg 8120, Level 16, State 1, Line 27
Column 'PROC_SEQUENCE.completed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 28
Column 'PROC_SEQUENCE.completed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Paul.  Your last suggestion did work, giving the required results which executes in the same time as what was suggested by appari.  I shall split the points between you both.

Thanks for help guys, appreciated.
:) thanks, was hoping it would save a few cpu cycles really - probably not visible for the small output - and mine was a derivative anyway so awarding to appari is entirely appropriate

if you can filter within that subquery providing the row_ref you should by the way - that was the real purpose behind my suggestion.