- 16 17 18 19 Select Product Category Name Count Order Id As Num Of Purchases From Dbo Olist Products Dataset Inner Joi 1 (95.55 KiB) Viewed 59 times
tables that I have....
Olist_order_reviews_dataset: review_id,
order_id, review_score, review_comment_title,
review_comment_message, review_creation_date,
review_answer_timestamp
Olist_products_dataset: product_id,
product_category_name, product_name_lenght,
product_description_lenght, product_photos_qty, product_weight_g,
product_length_cm
Olist_customers_dataset: cust_id,
zip_code_prefix, city, state
Olist_geolocation_dataset: cust_id,
zip_code_prefix, city, state
Olist_order_items_dataset: order_id,
order_item_id, product_id
Olist_order_payments_dataset: order_id,
payment_type, payment_value
Olist_orders_dataset: order_id, cust_id,
order_status, order_purchase_timestamp
Olist_sellers_dataset: seller_id,
zip_code_prefix, city, state
This is the query I came up with:
SELECT product_id as products, COUNT(DISTINCT
product_category_name) as categories, COUNT(DISTINCT review_id) AS
review_count
FROM olist_products_dataset
JOIN product_category_name ON
product_category_name.product_id = product_id
JOIN review_id ON product_id =
review_id.product_id
WHERE product_category_name = 10
GROUP BY product_id
16 17 18 19 SELECT product_category_name, count (order id) AS num_of_purchases FROM dbo.olist_products_dataset INNER JOIN dbo.olist_order_reviews_dataset ON dbo.olist_order reviews datase.review.comment_message=dbo.olist customers_dataset.customer_id INNER JOIN dbo.olist_order_payments_dataset ON dbo.olist_orders_dataset.order_id=dbo.olist_order_payments_dataset.order_id 20 21 22 > !!! dbo.olist customers.. > ! dbo.olist_geolocatio... > I dbo.olist_order_item... dbo.olist_order_pay- !! dbo.olist order revie... Columns review_id (nvarch... order_id (nvarcha... review score (int.... review_comment review_comment... review_creation_... Messages > review_answer_ti... Keys Constraints 11:59:22 AM > > > Triggers Indexes Started executing query at Line 17 Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "dbo.olist_order_reviews_datase.review_comment_message" could not be bound, Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "dbo.olist_customers_dataset.customer_id" could not be bound. Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "dbo.olist_orders_dataset.order_id" could not be bound. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'order_id'. Total execution time: 00:00:00.063 > > Statistics dbo.olist_orders_dat... i dbo.olist_products_d... Columns product_id (nvar... product_category... product_name_le... product_descript... product_photos_... product_weight_... product_length_... product_height_... product_width_c... Keys Constraints >