Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return (0) zero in SQL when no rows returned

Posted on 2013-05-14
11
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
LVL 39

Accepted Solution

by:
appari earned 1000 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 49

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 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 49

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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