SQL: How to get size of table in nested query

Question

See below query. I want to get the size of the table returned in the sub query but the .size does not work. Is there a way around this?

SELECT 
    (SELECT 
        ROUND(AVG(ts.data / ts.avglen), 0) AS 'size'
    FROM
        x.ddp_table_stats AS ts, x.ddp_table_names AS tn
    WHERE
        ts.tableid = tn.id
            AND ts.date = '2017-12-21'
            AND tn.name IN ('nic_stat' , 'agent_app_names')
    GROUP BY name).size -
    (SELECT 
        ROUND(AVG(ts.data / ts.avglen), 0) AS 'size'
    FROM
        x.ddp_table_stats AS ts, x.ddp_table_names AS tn
    WHERE
        ts.tableid = tn.id
            AND ts.date = '2017-12-19'
            AND tn.name IN ('nic_stat' , 'agent_app_names')
    GROUP BY name).size AS result;

refer below link https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database — mahendra, Jan 02 '18 at 12:24
I doubt if .size is a valid alias (mysqlworkbench doesn't think so) and you cannot alias prior to - (minus) which makes the second .size alias invalid also. — P.Salmon, Jan 02 '18 at 12:27

score 0 · Answer 1 · answered Jan 02 '18 at 12:27

You don't actually need a subquery here. Instead, we can use conditional aggregation and make a single pass over the two joined tables to get the result you want.

SELECT
    name,
    ROUND(AVG(CASE WHEN ts.date = '2017-12-21' THEN ts.data / ts.avglen END) -
    AVG(CASE WHEN ts.date = '2017-12-19' THEN ts.data / ts.avglen END), 0) AS size
FROM
    ddp_table_stats AS ts
INNER JOIN ddp_table_names AS tn
    ON ts.tableid = tn.id
WHERE
    tn.name IN ('nic_stat' , 'agent_app_names')
GROUP BY
    name;

Note that I also select the name since it makes general sense to select the column by which we are grouping. Also, I replaced your implicit join syntax with explicit INNER JOIN syntax.

score 0 · Answer 2 · answered Jan 02 '18 at 12:29

.size is not needed in query.

SELECT 
    (SELECT 
        ROUND(AVG(ts.data / ts.avglen), 0) AS 'size'
    FROM
        x.ddp_table_stats AS ts, x.ddp_table_names AS tn
    WHERE
        ts.tableid = tn.id
            AND ts.date = '2017-12-21'
            AND tn.name IN ('nic_stat' , 'agent_app_names')
    GROUP BY name) -
    (SELECT 
        ROUND(AVG(ts.data / ts.avglen), 0) AS 'size'
    FROM
        x.ddp_table_stats AS ts, x.ddp_table_names AS tn
    WHERE
        ts.tableid = tn.id
            AND ts.date = '2017-12-19'
            AND tn.name IN ('nic_stat' , 'agent_app_names')
    GROUP BY name) AS result;

SQL: How to get size of table in nested query

2 Answers2