« first day (2273 days earlier)   

3:32 AM
Congrats, @McNets
 
 
7 hours later…
10:27 AM
@MaxVernon Thank you sir.
 
10:40 AM
@McNets nice!
Regarding your answer:
0
A: Select data from three tables using sub query and group by

McNetsYour 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 ...

You provide a solution - which I think is correct - but the explanation is not entirely accurate
> SQL Server returns an error indicating that your subquery returns more than one value.
No, if you remove GROUP BY month from the main query, I think you'll get no error!
The subquery returns 1 row - assuming product_id is the PK in products
 
@ypercubeᵀᴹ yes you're right, let me rewrite the answer, but I'm not sure if he expects sales of products by month, or simply sales by month.
 
And I'm not sure now about my previous comment - about removing the inner GROUP BY.
I'll test and let you know. I think my comment was wrong.
 
@ypercubeᵀᴹ ok
 
Useful, if you're web-inclined: github.com/begriffs/postgrest
 
10:58 AM
@McNets just noticed that they also have put sales_fact in the FROM of the subquery, too.
I thought that the subquery was correlated but it isn't.
This is probably a mistake - and they did want a correlated subquery.
I suggest you rollback the edit with my suggestion. It depends on what they want - but it wouldn't work as it is.
 
@ypercubeᵀᴹ ok, let me ask the OP what's the purpose of his query
 
Yeah, that would be best.
See the error I was expecting (with a correlated subquery): dbfiddle.uk/…
 
And I think this order by:
ORDER BY
DATEPART(mm,CAST(the_month+ ' 1900' AS DATETIME)) asc;
will produce some issues too.
 
yeah, I wonder what values this_month has.
 
11:16 AM
ok, see you later.
 
 
8 hours later…
7:04 PM
@McNets nice one.
 
7:34 PM
@MichaelGreen 👍
 

« first day (2273 days earlier)