Solved

Return (0) zero in SQL when no rows returned

Posted on 2013-05-14
11
312 Views
Last Modified: 2013-05-14
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
Comment
Question by:aritasky
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 39

Expert Comment

by:appari
ID: 39166911
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
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39166919
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
 

Author Comment

by:aritasky
ID: 39166925
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
 
LVL 39

Expert Comment

by:appari
ID: 39166946
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
 

Author Comment

by:aritasky
ID: 39166950
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 39166985
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167010
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
 

Author Comment

by:aritasky
ID: 39167027
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39167049
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
 

Author Comment

by:aritasky
ID: 39167063
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167154
:) 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now