MySQL: VIEW Statements: Using Views and Aggregate functions

Question

I'm trying to use a view statement within a select statement, but at the same time I'm attempting to include an aggregate function which includes joins inside of the VIEW table.

What I'm not understanding is whether or not I need to rejoin the other tables. Why doesn't this work while using SalesData as a table?

The error I get is:

"SQL Error (1054): Unknown column 'SalesData.unit_price' in 'field list'

With this, I believe the error actually occurs here:

SUM(ROUND(SalesData.unit_price * SalesData.order_qty,2)) as 'Total Spent'

CREATE VIEW SalesData AS 
    SELECT orders.customer_id, 
           SUM(ROUND(items.unit_price * order_details.order_qty,2)) as 'Total Spent'
    FROM order_details
    JOIN orders on orders.order_id = order_details.order_id
    JOIN items on items.item_id = order_details.item_id
    GROUP BY orders.customer_id;

SELECT CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) as 'Customer',
           SUM(ROUND(SalesData.unit_price * SalesData.order_qty,2)) as 'Total Spent'
    FROM customers
    JOIN SalesData on SalesData.customer_id = customers.customer_id
    GROUP BY customer_id;

Can you try to alias the customer_id, like this? SELECT orders.customer_id AS customer_id — JustAPup
– JustAPup, Commented Apr 11, 2016 at 18:19
I can't see how that error is occurring. At no point did you reference unit_price qualified to the SalesData view - you only use items.unit_price. It's really that SQL statement causing the error? — Michael Berkowski
– Michael Berkowski, Commented Apr 11, 2016 at 18:21
Oh, I'm sorry I've been playing with the code and posted the wrong thing in there. The table I was using previously in the SUM function was SalesData. I will change it. But the same error occurs with this table being used as well. @MichaelBerkowski — Sierra
– Sierra, Commented Apr 11, 2016 at 18:22
@Minh I tried it, and it didn't change much in the way of our error. — Sierra
– Sierra, Commented Apr 11, 2016 at 18:24
Ok, so the SalesData view only exposes two columns in its SELECT list -- customier_id, Total Spent. Should it also be including unit_price, order_quantity column that you intend to use in the join, or should those be derived from an additional join to orders in your final query? — Michael Berkowski
– Michael Berkowski, Commented Apr 11, 2016 at 18:37

JustAPup · Accepted Answer · 2016-04-11 18:39:23Z

1

Well, it doesn't seem that unit_price is a column of SalesData, but looking at your question, it seems that all you need is this:

SELECT CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) as 'Customer',
       SalesData.[Total Spent]
FROM customers
JOIN SalesData on SalesData.customer_id = customers.customer_id;

answered Apr 11, 2016 at 18:39

JustAPup

1,79013 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sierra Over a year ago

Okay, this gives me questions. In normal standards, if you put a variable as in quotes (like so: customer_id as 'Customer ID') could you put it in something like you did? (SalesData.[Total Spent] . Is that a typically working thing? Unfortunately, it did not work in mine, but it led me to try other things!! When I changed SalesData.[Total Spent] to SalesData.Total_Spent (also changing the as statement in the VIEW) it did work. I guess I want to know why it doesn't work the way you did it...

JustAPup Over a year ago

In SQL Server, we use [ ] to denote a name that possibly contains a space between (as in your case, 'Total Spent'). For Oracle it's replaced by double quotes. So you'll probably need SalesData."Total Spent"

Collectives™ on Stack Overflow

MySQL: VIEW Statements: Using Views and Aggregate functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related