• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Return (0) zero in SQL when no rows returned

This question is in relation to a previous question (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28120239.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

0
aritasky
Asked:
aritasky
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
appariCommented:
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

0
 
santoshmotwaniCommented:
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
0
 
aritaskyAuthor Commented:
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
appariCommented:
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

0
 
aritaskyAuthor Commented:
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.
0
 
appariCommented:
sorry I forgot to change the select columns,
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 order_status, isnull(S1.Total,0) Total
FROM OrderSt Left Outer 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
               ) 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

0
 
PortletPaulCommented:
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
0
 
aritaskyAuthor Commented:
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.
0
 
PortletPaulCommented:
yes, sorry about that - maybe got too carried away... if this doesn't work stick with what does
;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
                 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
               , count(*) as total
            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 ON OrderSt.order_status = S1.NewStatus
ORDER BY OrderSt.sort_by ASC

Open in new window

0
 
aritaskyAuthor Commented:
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.
0
 
PortletPaulCommented:
:) 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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now