combine the results of two queries with different group by clause

Question

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.

score 1 · Answer 1 · answered Dec 08 '22 at 16:17

You can use UNION to combine two queries for IN and OUT dates:

WITH combined AS
(
SELECT in_date AS _date,
       COUNT(in_date) AS IN_COUNT,
       0 AS OUT_COUNT
       stock_type, 
FROM records 
GROUP BY in_date, 
         stock_type

UNION ALL

SELECT out_date,
       0 AS IN_COUNT
       COUNT(out_date) AS OUT_COUNT,
       stock_type, 
FROM records 
GROUP BY out_date, 
         stock_type

)

SELECT _date,
       SUM(IN_COUNT) AS IN_COUNT,
       SUM(OUT_COUNT) AS OUT_COUNT,
       stock_type
FROM combined
WHERE _date BETWEEN '2022-11-10' AND '2022-12-08' -- filtering date range
GROUP BY _date,
         stock_type

thanks for the solution. Even I tried this last night but ultimately realized, I need the solution which Jay provided below. Just wanted to check, since sql doesn't have those functions, how can we achieve that? — John, Dec 09 '22 at 11:14

score 0 · Accepted Answer · answered Dec 09 '22 at 01:14

0

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

answered Dec 09 '22 at 01:14

Jaytiger

11,626
2
5
15

thanks jay for the solution. but how can we achieve the same in sql as few functions of bq won't work there? – John Dec 09 '22 at 11:01
What DBMS are you using ? If it doesn't support *array* type, Dimi's answer has more chance that his query works there since it uses a standard SQL syntax. – Jaytiger Dec 09 '22 at 14:51
Its HeidiSQL. I want the results of two rows ina single row like yours for out_date and in_date. – John Dec 12 '22 at 04:30
From https://mariadb.com/kb/en/heidisql/ , *HeidiSQL is a Windows client for MariaDB and MySQL.* . HeidiSQL seems not a DBMS and you seems to use use MariaDB or MySQL. Then prefer to use Dimi's answer since my answer is for Google BigQuery. – Jaytiger Dec 12 '22 at 09:24

combine the results of two queries with different group by clause

2 Answers2