Group By Doubt in case of joins

I have one doubt. categories table in total have 3 columns.If we use group by, in slecet statement we can
chose that column which is present in group by or an aggregate function. If we consider below query,
how come category_id category_department_id category_name coumns are coming but in group by we have only
category_id. Confused in this.

%%sql

SELECT c.*,
round(sum(oi.order_item_subtotal)::numeric, 2) AS category_revenue
FROM categories c JOIN products p
ON c.category_id = p.product_category_id
JOIN order_items oi
ON p.product_id = oi.order_item_product_id
JOIN orders o
ON oi.order_item_order_id = o.order_id
WHERE to_char(o.order_date, ‘yyyy-MM’) = ‘2014-01’
AND o.order_status IN (‘COMPLETE’, ‘CLOSED’)
GROUP BY c.category_id
ORDER BY c.category_id
LIMIT 10;

Regards


Learn Spark 1.6.x or Spark 2.x on our state of the art big data labs

  • Click here for access to state of the art 13 node Hadoop and Spark Cluster