Mysql - Get count and date from table and count and date from another table in one query

JohnBeasley
October 2, 2024
23 views
1 vote
2 Answers

I have a query that gets a count and date from a table. It looks like this:

SELECT 
 date(`date`) as 'Date'
 , sb.`send_id`
 , count(`id`) as 'SUBS' 
FROM `table1`
WHERE `date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`date`), `send_id`;

This gets me results that look like this:

|    DATE    |   SEND_ID    |   SUBS    |
-----------------------------------------
| 2024-09-01 |  1111111     |    1900   | 
| 2024-09-01 |  2222222     |    1835   | 
| 2024-09-02 |  1111111     |    264    | 
| 2024-09-02 |  2222222     |    26     | 
-----------------------------------------

I have a second query that looks like this:

SELECT 
  date(`o_date`)
  , `o_send_id`
  , count(`id`) AS 'UNI'
FROM `table2`
WHERE `o_date` BETWEEN '2024-09-01%' AND '2024-09-30%'
GROUP BY date(`o_date`), `o_send_id`;

This gets me results that looks like this:

|    O_DATE    |   O_SEND_ID    |   UNITS  |
--------------------------------------------
| 2024-09-01   |    1111111     |    107   | 
| 2024-09-01   |    2222222     |    360   | 
| 2024-09-02   |    1111111     |    223   | 
| 2024-09-02   |    2222222     |    209   | 
--------------------------------------------

What I am trying to do is JOIN these table to get the SUB and UNITS displayed.

So the results would look something like this:

|    DATE    |   SEND_ID    |   SUBS    |   UNITS |
---------------------------------------------------
| 2024-09-01 |  1111111     |    1900   |   107   |
| 2024-09-01 |  2222222     |    1835   |   360   |
| 2024-09-02 |  1111111     |    264    |   223   |
| 2024-09-02 |  2222222     |    26     |   209   |
---------------------------------------------------

Here was my attempt to JOIN the tables on a single date:

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` BETWEEN '2024-09-01%' AND '2024-09-30%')
GROUP BY date(tb1.`date`), tb1.`send_id`;

It runs for a little bit but eventually conks out. I’m assuming it’s because there quite a few records (in the millions). I get an error that reads:

Error in processing request
Error code: 500
Error text: Internal Error (rejected)
It seems that the connection to server has been lost.
Please check your network connectivity and server status.

So I tried to run the same query but this time just use 1 date and 1 send_id, like this:

SELECT 
  date(tb1.`date`) as 'Date'
  , tb1.`send_id`
  , count(tb1.`id`) as 'SUBS' 
  , count(tb2.`id`) as 'UNITS'
FROM `table1` tb1
JOIN `table2` tb2 ON tb2.`o_send_id` = tb1.`send_id` AND tb2.`o_date` = tb1.`date`
WHERE (tb1.`date` LIKE '2024-09-30%') AND tb1.`send_id` = '1111111'
GROUP BY date(tb1.`date`), tb1.`send_id`;

This query runs, but I get a result that looks like this:

|    DATE    |   SEND_ID    |   SUBS     |   UNITS   |
------------------------------------------------------
| 2024-09-30 |  1111111     |    35960   |   35960   |
------------------------------------------------------

Where as the query was successful in running, the I’m not sure what happened to the SUBS and UNITS, as they came in with the same 36960 result. The totals should be different, and no way should they be that high.

Not sure what I did wrong.

In a previous question I asked, someone suggested that I post the table description.

Here is the description for table1:

| Field   |   Type        | Null | Key  | Default |         Extra       |
-------------------------------------------------------------------------
| id      | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| send_id | varchar(255)  |  YES |      |  NULL   |                     |   
| date    | datetime      |  YES |      |  NULL   |                     |
-------------------------------------------------------------------------

And here is the description of table2:

| Field     |   Type        | Null | Key  | Default |         Extra       |
---------------------------------------------------------------------------
| id        | int(11)       |  NO  |  PRI |  NULL   |  AUTO_INCREMENT     |  
| o_send_id | varchar(255)  |  YES |      |  NULL   |                     |    
| o_date    | datetime      |  YES |      |  NULL   |                     |
---------------------------------------------------------------------------

Nothing special in the descriptions of each table.

With all of this said, help me figure out how I can get the SUBS count and UNIT counts to display in the same query.

Thank you.

** EDIT **

The indexes for table1 are below.

And here are the indexes for table2.

I am only using the ID.

Tags: mysql

Answers

A composite index on (send_id, date) or perhaps ((date(date)), send_id) would likely improve performance of all your queries.

Rather than joining the tables, you can join your grouped queries.

Note: You don’t need to add a % to a between dates query, MySQL is probably ignoring it. Do explicitly cast it to date as recommended in the docs.

with table1_count as (
  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`date`), `send_id`
),
table2_count as (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
)
select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from table1_count t1c
left join table2_count t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs

The downside of this approach is if any days are missing from table1, but are in table2, they will not appear in the query.

Since you’re on MySQL 5.7 which does not have with, they can be rewritten using subqueries.

select 
  t1c.date,
  t1c.send_id,
  t1c.subs,
  t2c.uni
from (
  SELECT 
    date(`date`) as 'Date'
    , sb.`send_id`
    , count(`id`) as 'SUBS' 
    FROM `table1`
    WHERE `date` BETWEEN date('2024-09-01') AND date('2024-09-30')
    GROUP BY date(`date`), `send_id`
) t1c
left join (
  SELECT 
    date(`o_date`) as 'Date'
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN date('2024-09-01') AND date('2024-09-30')
  GROUP BY date(`o_date`), `o_send_id`
) t2c on 
  t1c.date = t2c.date and 
  t1c.send_id = t2c.o_send_id
order by t1c.date, t1c.subs

You can join your 2 queries as

select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`send_id`)
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(first.UNI,0) Uni
from
(  SELECT 
   date(`date`) as 'Date'
   , sb.`send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`)
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.send_id

If date or o_send_id is exists in 1 subquery output and not exists in another, you may lost rows.

Update1:

More reliable case

id	date	send_id
1	2024-09-01	100
2	2024-09-01	101
3	2024-09-02	101

id	o_date	o_send_id
1	2024-09-01	100
2	2024-09-01	101
3	2024-09-01	101
4	2024-09-11	104

select `date`
  ,send_id
  ,sum(Subs) subs
  ,sum(UNI)  Uni
from
(  SELECT date(`date`) as `date`, `send_id` , count(`id`) as 'SUBS' ,0 UNI
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
  union all
  SELECT date(`o_date`) `date` , `o_send_id` ,0 SUBS ,count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)u
group by `date`,send_id

date	send_id	subs	Uni
2024-09-01	100	1	1
2024-09-01	101	1	2
2024-09-02	101	1	0
2024-09-11	104	0	1

Update2:

In first example, we lost date=2024-09-11′ and send_id=104

select coalesce(first.Date,second.Date) as `date`
  ,coalesce(first.`send_id`,second.`o_send_id`) send_id
  ,coalesce(first.SUBS,0) Subs
  ,coalesce(second.UNI,0) Uni
  ,first.*, second.*
from
(  SELECT 
   date(`date`) as 'Date'
   , `send_id`
   , count(`id`) as 'SUBS' 
  FROM `table1`
  WHERE `date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`date`), `send_id`
 )first
left join (
  SELECT date(`o_date`) Date
    , `o_send_id`
    , count(`id`) AS 'UNI'
  FROM `table2`
  WHERE `o_date` BETWEEN '2024-09-01' AND '2024-09-30'
  GROUP BY date(`o_date`), `o_send_id`
)second on first.date=second.date and first.send_id=second.o_send_id

date	send_id	Subs	Uni	Date	send_id	SUBS	Date	o_send_id	UNI
2024-09-01	100	1	1	2024-09-01	100	1	2024-09-01	100	1
2024-09-01	101	1	2	2024-09-01	101	1	2024-09-01	101	2
2024-09-02	101	1	0	2024-09-02	101	1	null	null	null

fiddle

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Get count and date from table and count and date from another table in one query

Answers