How to merge two query in one query?

SQL++
,
1

First Query
SELECT
COUNT(CASE WHEN f1.confirm_status=2 THEN 1 END) verifed_f1,
COUNT(CASE WHEN f1.confirm_status=1 THEN 1 END) processing_f1,
COUNT(case when f1.confirm_status=3 THEN 1 END) reject_f1,
SPLIT(millis_to_tz(f1.created_at, “Asia/Jakarta”), “T”)[0] as date_f1
FROM test.test2.f1
group by SPLIT(millis_to_tz(f1.created_at, “Asia/Jakarta”), “T”)[0]
order by date_f1 desc

Result:
[
{
“date_f1”: “2022-10-15”,
“processing_f1”: 0,
“reject_f1”: 5,
“verifed_f1”: 10
},
{
“date_f1”: “2022-10-11”,
“processing_f1”: 2,
“reject_f1”: 0,
“verifed_f1”: 3
}]

Second Query
SELECT
COUNT(CASE WHEN f2.cf_st=2 THEN 1 END) verifed_f2,
COUNT(CASE WHEN f2.cf_st=1 THEN 1 END) processing_f2,
COUNT(case when f2.cf_st=3 THEN 1 END) reject_f2,
SPLIT(millis_to_tz(f2.create_at, “Asia/Jakarta”), “T”)[0] as date_f2
FROM test.test2.f2
group by SPLIT(millis_to_tz(f2.create_at, “Asia/Jakarta”), “T”)[0]
order by date_f2 desc

Result :
[
{
“date_f2”: “2022-10-11”,
“processing_f2”: 0,
“reject_f2”: 11,
“verifed_f2”: 2
},
{
“date_f2”: “2022-09-30”,
“processing_f2”: 0,
“reject_f2”: 0,
“verifed_f2”: 2
},

———————
Expected:
[
{
“group_date": “2022-10-15”,
“processing_f1”: 0,
“reject_f1”: 5,
“verifed_f1”: 10,
“processing_f2”: 0,
reject_f2": 0,
“verifed_f2”: 0
},
{
“group_date": “2022-10-11”,
“processing_f1”: 2,
“reject_f1”: 0,
“verifed_f1”: 3,
“processing_f2”: 0,
reject_f2”: 11,
“verifed_f2”: 2
},
{
“group_date": “2022-09-30”,
“processing_f1”: 0,
“reject_f1”: 0,
“verifed_f1”: 0,
“processing_f2”: 0,
reject_f2”: 0,
“verifed_f2”: 2
}
]

Thanks for your answer.

2

If you’re on version 7 you can use the format parameter to the date functions.

Something like this:

WITH a AS (SELECT
COUNT(CASE WHEN f1.confirm_status=2 THEN 1 END) verifed_f1,
COUNT(CASE WHEN f1.confirm_status=1 THEN 1 END) processing_f1,
COUNT(case when f1.confirm_status=3 THEN 1 END) reject_f1,
dt group_date
FROM default.s1.c1 f1 
LET dt = millis_to_tz(f1.created_at, "Asia/Jakarta", "YYYY-MM-DD")
group by dt
), b AS (
SELECT
COUNT(CASE WHEN f2.cf_st=2 THEN 1 END) verifed_f2,
COUNT(CASE WHEN f2.cf_st=1 THEN 1 END) processing_f2,
COUNT(case when f2.cf_st=3 THEN 1 END) reject_f2,
dt2 group_date
FROM default.s1.c2 f2 
LET dt2 = millis_to_tz(f2.create_at, "Asia/Jakarta", "YYYY-MM-DD")
group by dt2
)
(
SELECT a.verifed_f1
      ,a.processing_f1
      ,a.reject_f1
      ,IFMISSING(b.verifed_f2,0) verifed_f2
      ,IFMISSING(b.processing_f2,0) processing_f2
      ,IFMISSING(b.reject_f2,0) reject_f2
      ,a.group_date group_date
FROM a LEFT OUTER JOIN b ON a.group_date = b.group_date
)
UNION
(
SELECT IFMISSING(a.verifed_f1,0) verifed_f1
      ,IFMISSING(a.processing_f1,0) processing_f1
      ,IFMISSING(a.reject_f1,0) reject_f1
      ,b.verifed_f2
      ,b.processing_f2
      ,b.reject_f2
      ,b.group_date group_date
FROM b LEFT OUTER JOIN a ON a.group_date = b.group_date
)
ORDER BY group_date DESC
;

might suffice.

HTH.

1 Like
3

Thank you. May I ask in advance if I can create a date that doesn’t exist in the database? For non-existent days, enter all 0 values.
Example form above data
{
“group_date": “2022-10-15”,
“processing_f1”: 0,
“reject_f1”: 5,
“verifed_f1”: 10,
“processing_f2”: 0,
reject_f2": 0,
“verifed_f2”: 0
},
{
“group_date": “2022-10-14”,
“processing_f1”: 0,
“reject_f1”: 0,
“verifed_f1”: 0,
“processing_f2”: 0,
reject_f2": 0,
“verifed_f2”: 0
},
{
“group_date": “2022-10-13”,
“processing_f1”: 0,
“reject_f1”: 0,
“verifed_f1”: 0,
“processing_f2”: 0,
reject_f2": 0,
“verifed_f2”: 0
},
…
{
“group_date": “2022-10-11”,
“processing_f1”: 2,
“reject_f1”: 0,
“verifed_f1”: 3,
“processing_f2”: 0,
reject_f2”: 11,
“verifed_f2”: 2
},

]

4

You have to state the range.

In this example the arguments to DATE_RANGE_STR dictate the dates for which the query runs (or more specifically, the range for “f1” with gaps filled in):

WITH a AS (SELECT
COUNT(CASE WHEN IFMISSING(f1.confirm_status,-1)=2 THEN 1 END) verifed_f1,
COUNT(CASE WHEN IFMISSING(f1.confirm_status,-1)=1 THEN 1 END) processing_f1,
COUNT(case when IFMISSING(f1.confirm_status,-1)=3 THEN 1 END) reject_f1,
d group_date
FROM 
     date_range_str("2022-10-28","2022-11-05","day",1) d
          LEFT OUTER JOIN default.s1.c1 f1 ON millis_to_tz(f1.created_at, "Asia/Jakarta", "YYYY-MM-DD") = d
group by d
), b AS (
SELECT
COUNT(CASE WHEN f2.cf_st=2 THEN 1 END) verifed_f2,
COUNT(CASE WHEN f2.cf_st=1 THEN 1 END) processing_f2,
COUNT(case when f2.cf_st=3 THEN 1 END) reject_f2,
dt2 group_date
FROM default.s1.c2 f2 
LET dt2 = millis_to_tz(f2.create_at, "Asia/Jakarta", "YYYY-MM-DD")
group by dt2
)
(
SELECT a.verifed_f1
      ,a.processing_f1
      ,a.reject_f1
      ,IFMISSING(b.verifed_f2,0) verifed_f2
      ,IFMISSING(b.processing_f2,0) processing_f2
      ,IFMISSING(b.reject_f2,0) reject_f2
      ,a.group_date group_date
FROM a LEFT OUTER JOIN b ON a.group_date = b.group_date
)
UNION
(
SELECT IFMISSING(a.verifed_f1,0) verifed_f1
      ,IFMISSING(a.processing_f1,0) processing_f1
      ,IFMISSING(a.reject_f1,0) reject_f1
      ,b.verifed_f2
      ,b.processing_f2
      ,b.reject_f2
      ,b.group_date group_date
FROM b LEFT OUTER JOIN a ON a.group_date = b.group_date
)
ORDER BY group_date DESC
;

If you have too small a range it will not match all records from “f1” (but will still for “f2”, but without gaps filled). If you aren’t after a specific range but are after everything from the earliest f1 or f2 to the latest f1 or f2, you could simply SELECT the MAX and MIN date values for each and plug the earliest and latest into the DATE_RANGE_STR arguments.

1 Like
5

thanks for your anwser

6

I’ve been out of it for a bit, but I don’t see anything wrong with your second query. Can’t you just add the GROUP BY orders.product_id, products.product_name and the COUNT(orders.product_id) as “count” column and then you get your result?

1 Like
7

oh thank you for your answer