Reuse select aliases in MySQL

I currently have a query where I’m doing two subqueries to get X, Y data:

SELECT
  t.series AS week,
  ( ... ) X,
  ( ..., AND ... ) Y,
  ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp t

Y is kind of subset of X, since I apply just an additional condition to the existing ones, if X is:

SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY

Then Y has an additional AND condition:

SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
AND t1.some_state = 'x state'

And for the value of X I need to take those two results – X and Y and do some calculation. Since I can’t use the aliases, I have to use a subquery, right? But in that case it seems too much.

Is there a way to reuse those subqueries? It seems to be too much of the same.

series_tmp is a temporary table used to create a range of dates with a specific step:

CALL generate_series(DATE('2020-08-23' - INTERVAL 12 WEEK), '2020-08-23', 'INTERVAL 7 DAY');

SELECT * FROM series_tmp;

'2020-05-31'
'2020-06-07'
'2020-06-14'
'2020-06-21'
'2020-06-28'
'2020-07-05'
'2020-07-12'
'2020-07-19'
'2020-07-26'
'2020-08-02'
'2020-08-09'
'2020-08-16'
'2020-08-23'

The INNER JOIN used is the only one in the query which is just used to compare against primary/foreign key to further filter by the value of t2.something (t2.something = 1).

The columns I’m using from t1 are just date and some_state, the count is performed using the wildcard.

This is to reproduce the scenario:

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id)
);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  id int(11) NOT NULL AUTO_INCREMENT,
  t3_id int(11) NOT NULL,
  PRIMARY KEY (id)
);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  t2_id int(11) NOT NULL,
  created_at datetime NOT NULL,
  status varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO t3 (name) VALUES ('t3 #1');
INSERT INTO t2 (t3_id) VALUES (1);
INSERT INTO t1 (t2_id, status, created_at) VALUES   
('1', 'sent',      '2020-06-03 00:00:00'),
('1', 'sent',      '2020-06-08 00:00:00'),
('1', 'sent',      '2020-06-19 00:00:00'),
('1', 'responded', '2020-06-26 00:00:00'),
('1', 'responded', '2020-06-27 00:00:00'),
('1', 'sent',      '2020-06-30 00:00:00'),
('1', 'sent',      '2020-07-08 00:00:00'),
('1', 'sent',      '2020-07-09 00:00:00'),
('1', 'responded', '2020-07-14 00:00:00'),
('1', 'sent',      '2020-07-21 00:00:00'),
('1', 'sent',      '2020-07-22 00:00:00'),
('1', 'sent',      '2020-07-25 00:00:00'),
('1', 'responded', '2020-07-30 00:00:00'),
('1', 'sent',      '2020-08-02 00:00:00'),
('1', 'sent',      '2020-08-10 00:00:00'),
('1', 'responded', '2020-08-18 00:00:00'),
('1', 'responded', '2020-08-19 00:00:00');

-- expected result would be:

-- '2020-05-31','1','0','0.00'
-- '2020-06-07','1','0','0.00'
-- '2020-06-14','1','0','0.00'
-- '2020-06-21','2','2','100.00'
-- '2020-06-28','1','0','0.00'
-- '2020-07-05','2','0','0.00'
-- '2020-07-12','1','1','100.00'
-- '2020-07-19','3','0','0.00'
-- '2020-07-26','1','1','100.00'
-- '2020-08-02','1','0','0.00'
-- '2020-08-09','1','0','0.00'
-- '2020-08-16','2','2','100.00'

The SQLfiddle http://sqlfiddle.com/#!9/45f389.

I’m using MySQL 5.6 so I’m not able to use CTEs 🙁

PS: series_tmp comes from [this].

Answer

You can’t use CTEs in MySQL* but you can use temporary tables which is a close MySQL equivalent (not sure how you calculate Z so I didn’t put that one in):

CREATE TEMPORARY TABLE temp_t3 AS (
    SELECT t1.created_at, t1.status
    FROM t1
    INNER JOIN t2 ON t2_id = t2.id
    WHERE t2.t3_id = 1
);

SELECT
    t.series AS week,
    (SELECT COUNT(*) FROM temp_t3 WHERE created_at BETWEEN t.series AND t.series + INTERVAL 6 DAY) X,
    (SELECT COUNT(*) FROM temp_t3 WHERE created_at BETWEEN t.series AND t.series + INTERVAL 6 DAY AND status = 'responded') Y
FROM series_tmp t;

*prior to version 8 (https://dev.mysql.com/doc/refman/8.0/en/with.html)

Advertisement

Answer