CODEWITHSUNDEEP
mysql
Ali Im
Ali Im

Reputation: 25

mysql multi filter into 1 view

i have 2 tables as bellow

table1 - the entry data is made by production planner .(main order data)

orderid production_date material userid
123 2021-05-07 leather user1
325 2021-05-07 leather user2
326 2021-05-08 leather user3
327 2021-05-08 fabric user4

table2 the entry data is by worker entry as out (finish task)

orderid production_date production_type userid timestamp prouction_status
123 2021-05-07 leather user1 2021-05-07 in
325 2021-05-08 leather user2 2021-05-07 out
326 2021-05-08 leather user3 2021-05-07 out

i want to make a single table with multi filters and calcs as bellow

number 1

select table1.production_date  , 
          count(*) as total_task1 from table1

filter total orders

number 2

select table1.production_date  , 
      count(*) as total_task  from table1
where table1.production_type = "leather"

total order filtered by "leather"

number 3

select table2.production_date  , 
          count(*) as total_out  from table1
    where table2.production_type = "leather"

total task filter by leather (total in and total out)

at the end i want to combine those examples to get table or view as below bellow

production_date total_order total_leather_order total_in total_out balance
2021-05-08 4 3 1 2 1

balance will be total_leather_out- total_order = balance (not processed yet)

and group by production_date = CURDATE()

is it possible ?any one can give me a hint . thank u

Upvotes: 0

Views: 63

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can do:

select
  o.*, 
  e.*,
  o.total_order - o.total_leather_order as balance
from (
  select
    count(*) as total_order,
    sum(case when material = 'leather' then 1 else 0 end) as total_leather_order
  from table1
) o
cross join (
  select
    sum(case when production_status = 'in' then 1 else 0 end) as total_in,
    sum(case when production_status = 'out' then 1 else 0 end) as total_out,
  from table2
) e

Note: I didn't quite understand the logic to compute the balance. Nevertheless, the subtotals are now there, so you can compute it as needed.

Upvotes: 1

Related Questions