I have this sql query where I want to retrieve the sum of the amount of sales in August 2005. Whenever I run this, I receive the error "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I fix this so it displays all columns and display the sum in my repsales.amount column?
SELECT *, SUM(repsales.amount) AS amount
FROM repsales
WHERE repsales.saledate BETWEEN '2005-08-01' AND '2005-08-31'
You can use window functions:
SELECT rs.*, SUM(rs.amount) OVER () AS total_amount
FROM repsales rs
WHERE rs.saledate BETWEEN '2005-08-01' AND '2005-08-31';
My answer is using SUM as an analytic/window function. This means that each row will be assigned a value, as contrasted to using SUM with GROUP BY, where an aggregation of rows takes place.
Using SUM(amount) OVER () defines the window to be the entire table. That is, the sum will be the total sum of the amount.
Another alternative solution is to state the column names one by one in the select and groupby statements. I.E. the example below groups by the data based department and category:
select rs.department,rs.category,sum(rs.amount) as SumAmount
from repsales rs
where rs.saledate BETWEEN '2005-08-01' AND '2005-08-31';
group by rs.department, rs.category
If you need all columns, you can save the resultset of the query above in a temp table (select ... into #temp from ...) and use it like:
select rs.*,t.SumSmount
from repsales rs
inner join #temp t on t.department=rs.department and t.category=rs.category
insert the sum of amount into a temp table with the unique value columns dept,category etc ...
Select rs.salespersonID, rs.OtherUniqueColumes, Sum(rs.amount) AS Amout
INTO #tempSales FROM repsales
WHERE repsales.saledate BETWEEN '2005-08-01' AND '2005-08-31'
Group By rs.salespersonID, rs.OtherUniqueColumes
join the temp table with the original table to get all other columns
SELECT rs.*, t.Amount
FROM repSales rs INNER JOIN #tempSales t on rs.salespersonID = t.salespersonID AND rs.OtherUniqueColumes = t.OtherUniqueColumes
DROP TABLE #tempSales
