Reputation: 235
There are two sql queries, I want to write as one query. the second query display the count of distinct records where action_text like '%STAFF%'. I tried using UNION but It did not work.
select date(dated) date_ord,
count(DISTINCT order_number) as orders_placed,
sum(case when action_text like '%STAFF%' then 1 else 0 end) AS orders_staff,
sum(case when action_text in (
'CBA Capture attempt',
'GCO Capture attempt',
'PPP Capture',
'PPE Capture',
'Staff CC capture',
'Web CC capture',
'Staff Finance WIRE authorized',
'Staff Finance PO authorized',
'Staff Finance COD authorized',
'Authorized The CPIC') then 1 else 0 end) AS orders_manuallycaptured
from stats.sales_actions
group by date_ord
order by dated desc
SELECT COUNT(DISTINCT order_number) as unique_orderstouched,
date(dated) date_ords
FROM sales_actions
WHERE action_text like '%STAFF%'
group by date_ords
order by dated desc
Upvotes: 0
Views: 115
Reputation: 56935
As far as I can tell, the only new column in the second query is COUNT(DISTINCT order_number) ... WHERE action_text LIKE '%STAFF%'
.
Then you can just add in a COUNT(DISTINCT IF(action-text LIKE '%STAFF%',order_number,NULL)) as unique_orderstouched
to your original query.
(Also I assume the table stats.sales_actions
in your first query is the same as the table sales_actions
in your second query?).
You'd end up with:
select date(dated) date_ord,
count(DISTINCT order_number) as orders_placed,
sum(case when action_text like '%STAFF%' then 1 else 0 end) AS orders_staff,
sum(case when action_text in (
'CBA Capture attempt',
'GCO Capture attempt',
'PPP Capture',
'PPE Capture',
'Staff CC capture',
'Web CC capture',
'Staff Finance WIRE authorized',
'Staff Finance PO authorized',
'Staff Finance COD authorized',
'Authorized The CPIC') then 1 else 0 end) AS orders_manuallycaptured,
-- new line follows
COUNT(DISTINCT IF(action-text LIKE '%STAFF%',order_number,NULL))
AS unique_orderstouched
from stats.sales_actions
group by date_ord
order by dated desc
The COUNT( DISTINCT IF( condition, order_number, NULL ) )
is like saying COUNT( DISTINCT order_number ) ... WHERE <condition>
-- you can also think of it as being like your SUM( CASE WHEN condition THEN 1 ELSE 0)
, but with a DISTINCT
in it.
Upvotes: 1
Reputation: 2644
If you do a union the columns you select need to be the same between the two queries. You need to have the same number of columns, same data types, and they need to be in the same order. In the first query you are selecting four columns and the second query only two.
Upvotes: 0