sqlgoogle-bigqueryheidisql

combine the results of two queries with different group by clause

I have a use case where I want to calculate the number of dates which had stocks in and out. My tbl schema has just 4 cols : stock_type, in_date, out_date, serial_no.

stck_typ in_date out_date serial_no
S1 2022-11-10 2022-11-18 103
S3 2022-11-13 2022-11-25 104
S2 2022-12-01 2022-12-07 102

tbl structure: enter image description here

Is there a way to get the output like below tbl:

date in_count out_count stock_type
2022-11-10 3 0 S1
2022-12-01 6 0 S2
2022-12-01 6 0 S1
2022-12-01 6 0 S3
2022-12-05 0 8 S1
2022-12-05 0 8 S2

Any help will be appreciated.

I tried the below query on heidisql : For IN_DATE:

SELECT in_date, stock_type, COUNT(in_date) AS IN_COUNT FROM records GROUP BY IN_DATE having in_date>='2022-11-10' AND in_date<='2022-12-08'

For OUT_DATE:

SELECT out_date, stock_type,  COUNT(OUT_date) AS OUT_COUNT FROM records GROUP BY OUT_DATE having out_date>='2022-11-10' AND out_date<='2022-12-08'

or something like this on bigquery:

SELECT in_date,
    a.stock_type,
    count(in_date) OVER(PARTITION BY in_date)
FROM table 

SELECT in_date,
    a.stock_type,
    count(in_date) OVER(PARTITION BY in_date)
FROM table

the output looks correct but I want the output in a single query but grouping by on both in_date and out_date is not giving the desired result.

Solution

  • You might consider another option not using UNION as well,

    SELECT date, stock_type, COUNTIF(offset = 0) in_count, COUNTIF(offset = 1) out_count
  FROM records, UNNEST([in_date, out_date]) date WITH offset
 WHERE date BETWEEN '2022-11-10' AND '2022-12-08'
 GROUP BY 1, 2 ORDER BY 1, 2;

    Query results

    enter image description here