CODEWITHSUNDEEP
sql
rkp_13
rkp_13

Reputation: 1

multiple dates against count

I have 3 columns

So, if i distinctly count all the order_IDs on Order_Received_Date, I will get "Number of Orders" Similarly, if i distinctly count all the order_IDs on Activation_Date, I will get "Number of Activations"

What i want is two columns called "# Orders" and "# Activations"

Appreciate any inputs, thanks

Upvotes: 0

Views: 37

Answers (2)

HGF
HGF

Reputation: 448

If I understand you correctly, I guess the below query might help you solve this issue:

SELECT 
    OrderID,
    ActivationDate,
    OrderReceivedDate,
    COUNT(OrderID) OVER (),
    COUNT(ActivationDate) OVER ()
FROM
(
    SELECT 1 AS OrderID, '2014-01-01' AS ActivationDate, '2013-12-15' AS OrderReceivedDate UNION
    SELECT 2 AS OrderID, NULL AS ActivationDate, '2013-12-19' AS OrderReceivedDate UNION
    SELECT 3 AS OrderID, '2014-03-01' AS ActivationDate, '2013-12-17' AS OrderReceivedDate UNION
    SELECT 4 AS OrderID, '2014-04-01' AS ActivationDate, '2013-12-03' AS OrderReceivedDate
) t

I wish this helps you...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I use union all for this type of calculation. The following is standard SQL so it should work in any database:

select thedate, sum(r) as numorders, sum(a) as numactivations
from (select activation_date as thedate, 1 as a, 0 as r from table t
      union all
      select order_received_date, 0, 1 from table t
     ) t
group by thedate
order by thedate;

Upvotes: 1

Related Questions