CODEWITHSUNDEEP
mysql
nathan rivera
nathan rivera

Reputation: 225

How to query/SELECT from a view table?

I created a view table for which I am trying to query from

drop view if exists salesdata;

create view SalesData as
select orders.order_id, items.item_id, (order_details.order_qty *   items.unit_price) as ItemTotal, items.title, artists.artist_id
from orders
join order_details
on orders.order_id = order_details.order_id
join items
on order_details.item_id = items.item_id
join artists
on items.artist_id = artists.artist_id
order by artists.artist_id
;

select * from salesdata;

select artists.artist_id, sum(order_details.order_qty * items.unit_price) as TotalSales
from SalesData;

However whenever I try to complete the query

select artists.artist_id, sum(order_details.order_qty * items.unit_price) as TotalSales
from SalesData;

ill get the following error

Error Code: 1054. Unknown column 'artists.artist_id' in 'field list'

Upvotes: 0

Views: 54

Answers (2)

Eric BELLION
Eric BELLION

Reputation: 626

You have to select fields that are described in your view, try : 

select artists.artist_id, sum(ItemTotal) as TotalSales from SalesData;

Upvotes: 1

ck1
ck1

Reputation: 5443

The column names in the view don't include the table name prefixes from the create view ... as select statement, so don't include them when querying the view:

select artist_id, sum(order_qty * unit_price) as TotalSales
from SalesData;

You can also see the column names in the view using the following command:

describe SalesData;

Upvotes: 0

Related Questions