I very new to SQL (less than 100Hrs). Problem case is as mentioned below. Every time I try a query either get incorrect output or error that “not contained in either an aggregate function or the GROUP BY clause” Have tried searching similar questions or example but no results. I am lost now. Please help
I have three tables
Table Calc,
Source_id | date(yyyymmdd) | metric1 | metric 2 ------------------------------------------------- 1 | 20201010 | 2 | 3 2 | 20201010 | 4 | 5 3 | 20201010 | 6 | 7 1 | 20201011 | 8 | 9 2 | 20201011 | 10 | 11 3 | 20201011 | 12 | 13 1 | 20201012 | 14 | 15 2 | 20201012 | 16 | 17 3 | 20201012 | 18 | 19
Table Source
Source_id | Description ------------------------ 1 | ABC 2 | DEF 3 | XYZ
Table Factor
Date | Factor ----------------- 20201010 | .3 20201011 | .5 20201012 | .7
If selected dates by user is 20201010 to 20201012 then result will be
Required result
Source_id | Calculated Value ------------------------------------------------------------------------------- ABC | (((2x3)x.3 + (8x9)x.5 + (14x15)x.7))/(No of dates selected in this case =3) DEF | (((4x5)x.3+ (10x11)x.5 + (16x17)x.7))/(No of dates selected in this case =3) XYZ | (((6x7)x.3+ (12x13)x.5 + (18x19)x.7))/(No of dates selected in this case =3)
Dates will be user defined input so the calculated value should be average of that many dates. Selected dates will always be defined in range rather than random multiple selection.
In table calc, source_id and date together will be unique. Each date has factor which is to be multiplied with all source_id for that date.
If selected dates by user is from 20201010 to 20201011 then result will be
Source_id | Calculated Value ------------------------------------------------------------------------------- ABC | ((2x3)x.3+(8x9)x.5)/2 DEF | ((4x5)x.3+(10x11)x.5)/2 XYZ | ((6x7)x.3+(12x13)x.5)/2
If selected dates by user is 20201012 then result will be
Source_id | Calculated Value ------------------------------------------------------------------------------- ABC | (14x15)x.7 DEF | (16x17)x.7 XYZ | (18x19)x.7
Advertisement
Answer
Create a CTE to store the starting and ending dates and cross join it to the join of the tables, group by source and aggregate:
WITH cte AS (SELECT '20201010' min_date, '20201012' max_date)
SELECT s.Description,
ROUND(SUM(c.metric1 * c.metric2 * f.factor / (DATEDIFF(day, t.min_date, t.max_date) + 1)), 2) calculated_value
FROM cte t CROSS JOIN Source s
LEFT JOIN Calc c ON c.Source_id = s.Source_id AND c.date BETWEEN t.min_date AND t.max_date
LEFT JOIN Factor f ON f.date = c.date
GROUP BY s.Source_id, s.Description
See the demo.
Results:
> Description | calculated_value > :---------- | ---------------: > ABC | 61.60 > DEF | 83.80 > XYZ | 110.00