predefining derived tables mysql

Question

I have a table that called inventory_history_report which has a column detailed_disposition which can have one of three values SELLABLE, DEFECTIVE, CUSTOMER_DAMAGE.

I am trying to aggregate the rows into one row that will hold for each date the quantities for sellable, deffective and customer_damage. I need to group them by company, at fnsku.

This is what I came up with:

SELECT s1.company, 
    s1.fnsku, 
    s1.date, 
    s1.quantity AS defective_q, 
    s2.quantity AS sellable_q,
    s3.quantity AS customer_damage_q
FROM (
    SELECT id, sum(quantity) as quantity , company, date, fnsku, 
        detailed_disposition
    FROM inventory_history_report aihr 
    WHERE detailed_disposition = 'DEFECTIVE'
    GROUP BY date, company, fnsku, detailed_disposition
    ) AS s1
LEFT JOIN (
    SELECT id, sum(quantity) AS quantity , company, date, fnsku, 
        detailed_disposition
    FROM inventory_history_report aihr 
    WHERE detailed_disposition = 'SELLABLE'
    GROUP BY date, company, fnsku, detailed_disposition
    ) as s2 on (s2.fnsku = s1.fnsku AND 
                s2.company = s1.company AND
                s2.date = s1.date
                )
LEFT JOIN (
    SELECT id, sum(quantity) as quantity , company, date, fnsku, 
        detailed_disposition
    FROM inventory_history_report aihr 
    WHERE detailed_disposition = 'CUSTOMER_DAMAGED'
    GROUP BY date, company, fnsku, detailed_disposition
    ) AS s3 ON (s3.fnsku = s1.fnsku AND 
                s3.company = s1.company AND
                s3.date = s1.date
                )

I'm repeating myself in this query and I'm trying to find a way to reuse the derived table I use or at least part of it. I was trying to find a way to predefine a table but couldn't find a way, something like aliasing a derived table:

SELECT id, sum(quantity) as quantity , company, date, fnsku, detailed_disposition
FROM inventory_history_report ihr 
GROUP BY date, company, fnsku, detailed_disposition

and then filter it to my needs inside each join.

I tried to use some kind of a view like this:

CREATE VIEW aggregated_history AS
SELECT *
FROM amazon_inventory_history_report
GROUP BY date, company, fnsku, detailed_disposition

But I got an error denying me from doing it:

SQL Error [1142] [42000]: CREATE VIEW command denied to user for table 'aggregated_history'

I've also tried to use the WITH command but this as well gives error, that after some research got me to understand I am using version 5.7 which doesn't support the WITH keyword.

I cannot update the mysql server so this one is off the table nor can I elevate my permissions for this database.

Is there another way to do it, that will be less repetitive, more readable and preferable more efficient ?

WITH is the feature designed to do this. Do note that 5.7 end of life is Oct 2023, pretty soon now, and security updates may not be available after that date. — ysth, Jun 26 '21 at 03:06

score 1 · Accepted Answer · answered Jun 26 '21 at 05:12

1

The only other solution I can think of is to create a temporary table, but I am going to predict you don't have privilege to CREATE TEMPORARY TABLE either.

Look, if you know there are features available to do what you want, but you refuse to change your environment to employ those features, what do you expect to get as an answer?

answered Jun 26 '21 at 05:12

Bill Karwin

538,548
86
673
828

Thanks for the answer! I do not reuse to change anything, I’m just bound to the environment I get from my workplace. Crating temporary table may be a fair solution. For some odd reasons I managed to create a table. Is there a way to do it while running the query? – barshopen Jun 26 '21 at 07:39
If you did not use the `TEMPORARY` keyword, then you will create a regular table. No, you cannot create a table or a temporary table as part of the same query that uses it. `CREATE TABLE`, temporary or otherwise, is a separate SQL statement you must run prior to a query that uses that table. – Bill Karwin Jun 26 '21 at 16:41
Ok, thanks. I was just trying to make sure what my options are. This cleared them out for me. – barshopen Jun 26 '21 at 19:36

predefining derived tables mysql

1 Answers1