CODEWITHSUNDEEP
sql
deepak gupta
deepak gupta

Reputation: 41

Retrieve the total number of orders made and the number of orders for which payment has been done

Retrieve the total number of orders made and the number of orders for which payment has been done(delivered).

TABLE ORDER

------------------------------------------------------
ORDERID QUOTATIONID     STATUS  
----------------------------------------------------
Q1001   Q1002           Delivered
O1002   Q1006           Ordered                         
O1003   Q1003           Delivered               
O1004   Q1006           Delivered               
O1005   Q1002           Delivered               
O1006   Q1008           Delivered               
O1007   Q1009           Ordered                             
O1008   Q1013           Ordered

Unable to get the total number of orderid i.e 8 

select count(orderid) as "TOTALORDERSCOUNT",count(Status) as "PAIDORDERSCOUNT" 
  from orders 
 where status ='Delivered'

The expected output is

TOTALORDERDSCOUNT     PAIDORDERSCOUNT
      8                     5

Upvotes: 2

Views: 5887

Answers (6)

SONALI DATTATRAYA TAMBOLI
SONALI DATTATRAYA TAMBOLI

Reputation: 7

There is no need where condition

select count(orderid) as "TOTALORDERSCOUNT", count(pymtmode)as "PAIDORDERSCOUNT" FROM ORDERS

Upvotes: 0

Will Jackson
Will Jackson

Reputation: 58

    SELECT DISTINCT COUNT(ORDERID) AS [TOTALORDERSCOUNT],
                    COUNT(CASE WHEN STATUS = 'ORDERED' THEN ORDERID ELSE NULL END) AS [PAIDORDERCOUNT]
    FROM ORDERS

TotalOrdersCount will count all distinct values in orderID while the case statement on PaidOrderCount will filter out any that do not have the desired Status.

Upvotes: 0

noPlaceLike127'0'0'1
noPlaceLike127'0'0'1

Reputation: 1

What I've used in the past for summarizing totals is 

SELECT
count(*) 'Total Orders',
sum( iif( orders.STATUS = 'Delivered', 1, 0 ) ) 'Total Paid Orders'
FROM orders

I personally don't like using CASE WHEN if I don't have to. This logic may look like its a little too much for a simple summation of totals, but it allows for more conditions to be added quite easily and also just involves less typing, at least for what I use this regularly for.

Using the iif( statement to set up the conditional where you're looking for all rows in the STATUS column with the value 'Delivered', with this set up, if the status is 'Delivered', then it marks it stores a value of 1 for that order, and if the status is either 'Ordered' or any other value, including null values or if you ever need a criteria such as 'Pending', it would still give an accurate count.

Then, nesting this within the 'sum' function totals all of the 1's denoted from your matched values. I use this method regularly for report querying when there's a need for many conditions to be narrowed down to a summed value. This also opens up a lot of options in the case you need to join tables in your FROM statement.

Also just out of personal preference and depending on which SQL environment you're using this in, I tend to only use AS statements for renaming when absolutely necessary and instead just denote the column name with a single quoted string. Does the same thing, but that's just personal preference.

As stated before, this may seem like it's doing too much, but for me, good SQL allows for easy change to conditions without having to rewrite an entire query.

EDIT** I forgot to mention using count(*) only works if the orderid's are all unique values. Generally speaking for an orders table, orderid is an expected unique value, but just wanted to add that in as a side note.

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT COUNT(ORDERID) TOTALORDERDSCOUNT, 
SUM(CASE WHEN STATUS = 'Delivered' THEN 1 ELSE 0 END ) PAIDORDERSCOUNT
FROM ORDER

You can also use COUNT in place of SUM as below-

SELECT COUNT(ORDERID) TOTALORDERDSCOUNT, 
COUNT(CASE WHEN STATUS = 'Delivered' THEN 1 ELSE NULL END ) PAIDORDERSCOUNT
FROM ORDER

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

you could use cross join between the two count 

select count(orderid) as TOTALORDERSCOUNT, t.PAIDORDERSCOUNT 
from  orders 
cross  join (
  select  count(Status) PAIDORDERSCOUNT 
  from orders where Status ='Delivered'
)  t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think you want conditional aggregation:

select count(*) as TOTALORDERSCOUNT,
       sum(case when status = 'Delivered' then 1 else 0 end) as PAIDORDERSCOUNT
from orders;

Upvotes: 6

Related Questions