Sql-server – Select data from three tables using sub query and group by

sql server

I would like to write a query for SQL Server to select “Sales” by “month”
(Sales from one table[sales_fact] and month from another table[time_by_day])
where sales is grouped by “ product_name"
and it is again form another table[product] i.e. sales/product by month. Here is my query:

SELECT 
    time_by_day.the_month , 
    ( SELECT sum(store_sales) 
      FROM sales_fact
        INNER JOIN product 
        ON  sales_fact.product_id = product.product_id
      GROUP BY product_name
    )
    AS total_sales 
FROM sales_fact 
  INNER JOIN time_by_day 
  ON  sales_fact.time_id = time_by_day.time_id
GROUP BY 
    time_by_day.the_month 
ORDER BY 
    DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

But it is not working. Getting error for the subquery. Can I get some advice pls?

Best Answer

Your subquery:

SELECT     sum(store_sales) 
FROM       sales_fact
INNER JOIN product 
ON         sales_fact.product_id = product.product_id 
GROUP BY   product_name

returns a list of aggregate sum by product.

But your main query expects only one value due you're grouping by month.

SELECT     time_by_day.the_month 
FROM       sales_fact 
INNER JOIN time_by_day 
ON         sales_fact.time_id = time_by_day.time_id
GROUP BY   time_by_day.the_month 
ORDER BY   DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

SQL Server returns an error indicating that your subquery returns more than one value.

If you want aggregate sales by product, instead of a subquery, join your products table on your main query on this way:

SELECT     t.the_month,
           p.product_id,
           p.product_name, 
           sum(store_sales) as total_sales 
FROM       sales_fact 
INNER JOIN time_by_day 
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month, p.product_id. p.product_name 
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

However if you need total sales by month, remove prduct.id and product.name from both, SELECT and GROUP BY statements.

SELECT     t.the_month,
           sum(store_sales) as total_sales_month 
FROM       sales_fact 
INNER JOIN time_by_day 
ON         sales_fact.time_id = time_by_day.time_id
INNER JOIN product p
ON         s.product_id = p.product_id
GROUP BY   t.the_month 
ORDER BY   DATEPART(mm,CAST(t.the_month + ' 1900' AS DATETIME)) ASC;

If your time_by_day table has a datetime field, you can use it on your ORDER BY clause instead of build a datetime value. In fact if you're filtering more than one year, actual ORDER BY clause do not work properly. 

ORDER BY   time_by_day.date_field ASC;

If there is no datetime value, you can order by month without cast.

ORDER BY   t.the_month ASC;
Related Question