CODEWITHSUNDEEP
postgresql
Lotarc
Lotarc

Reputation: 99

Count all results at one

I have queries with UNION ALL it looks like this, but then going same queries but with other conditionals

select count(id) filter (where data like '%"pmn":"CHILD_SEAT"%') as "Summary",
       count(id) filter (where data like '%"pmn":"CHILD_SEAT"%' and completed is true) as "Summary Completed",
       'Child Seat' "Type of car-class/seat"
from archived_order 
where created_user_login like 'exchangeAgent@shara' 
  and created between current_date - interval '1 day' and current_date

UNION ALL

select count(id) filter (where data like '%"pmi":2568%'),
       count(id) filter (where data like '%"pmi":2568%' and completed is true),
       'Child seat economy 1-3'
from archived_order 
where created_user_login not like 'exchangeAgent@shara' 
  and data like '%"cci":4%' 
  and created between current_date - interval '1 day' and current_date

UNION ALL

select count(id) filter (where data like '%"pmi":2568%') as a,
       count(id) filter (where data like '%"pmi":2568%' and completed is true),
       'Child seat standart 1-3'
from archived_order 
where created_user_login not like 'exchangeAgent@shara' 
  and data like '%"cci":1%' 
  and created between current_date - interval '1 day' and current_date;

I've tried to count by doing as and tried to select it with count, but it seems not working. I don't know exactly how to count for 2 columns all entries like this count(id) filter (where data like '%"pmi":2568%')

and all entries like this count(id) filter (where data like '%"pmi":2568%' and completed is true)

My goal is to get 2 numbers where will be a sum of all the values from each count.

 Summary | Summary Completed | Type of car-class/seat
---------+-------------------+-------------------------
   12899 |             10653 |
      97 |                94 | Child seat standart 1-3
      18 |                12 | Child seat economy 1-3

Upvotes: 0

Views: 33

Answers (1)

forpas
forpas

Reputation: 164069

Use a CTE on which you can get the sums:

with cte as (
select count(id) filter (where data like '%"pmn":"CHILD_SEAT"%') as "Summary",
       count(id) filter (where data like '%"pmn":"CHILD_SEAT"%' and completed is true) as "Summary Completed",
       'Child Seat' "Type of car-class/seat"
from archived_order 
where created_user_login like 'exchangeAgent@shara' 
  and created between current_date - interval '1 day' and current_date

UNION ALL

select count(id) filter (where data like '%"pmi":2568%'),
       count(id) filter (where data like '%"pmi":2568%' and completed is true),
       'Child seat economy 1-3'
from archived_order 
where created_user_login not like 'exchangeAgent@shara' 
  and data like '%"cci":4%' 
  and created between current_date - interval '1 day' and current_date

UNION ALL

select count(id) filter (where data like '%"pmi":2568%') as a,
       count(id) filter (where data like '%"pmi":2568%' and completed is true),
       'Child seat standart 1-3'
from archived_order 
where created_user_login not like 'exchangeAgent@shara' 
  and data like '%"cci":1%' 
  and created between current_date - interval '1 day' and current_date
)
select sum("Summary") "Summary", sum("Summary Completed") "Summary Completed", null
from cte
union all
select * from cte

Upvotes: 1

Related Questions