SUM strings, number of returns and order by date
Sorry to get stuck with this query, since I have data in the order by clause, does this tell me that I need to put an aggregate or group conditionally? (even if I don't need this aggregate value?).
UserData table (userID, sales, credits, dateCreated)
My query should return the last 10 results:
SELECT TOP 10 COUNT (*) as totalDays, SUM (sales), SUM (credits) FROM UserData WHERE userID = @userID ORDER BY dateCreated DESC
I have totalDays because it might not return the number of days I asked for (in this case it is 10, but it can be changed later).
This gives you the totals for the last 10 days:
SELECT
COUNT(*) as totalDays, SUM(sales), SUM(credits)
FROM
UserData
WHERE
userID = @userID
AND DateCreated > GETDATE() - 10
Last 10 sales
SELECT COUNT(*) as totalDays, SUM(sales), SUM(credits)
FROM
(SELECT TOP 10 sales, credits
FROM UserData
WHERE userID = @userID
ORDER BY dateCreated DESC) X
a source to share
It makes no sense to use top
or order by
for a query that only returns one row in the result. You must first make the query return more than one row in order to use them.
It just aggregates all the sales and returns one row, so you need to do something first:
select count(*) as totalDays, sum(sales), sum(credits)
from UserData
where userID = @userID
If you want to take the last ten sales and summarize, you need a subquery that isolates ten sales first, and then you can fill them in:
select count(*) as totalDays, sum(sales), sum(credits)
from (
select top 10 sales, credits
from UserData
where userID = @userID
order by dateCreated desc
) LastData
If you want to sum every day the refund for the last ten days, you need to group by date:
select top 10 count(*) as totalDays, sum(sales), sum(credits)
from UserData
where userID = @userID
group by dateCreated
order by dateCreated desc
a source to share
This is due to the * (ALL) field. Since you are aggregating you have to select a different field to count, any field will do in your case, which will make your query something like this:
SELECT TOP 10 COUNT (USERID) AS TOTALDAYS, SUM (SALES), SUM (CREDITS) FROM USERDATA WHERE userid = @userid GROUP BY datecreated ORDER BY datecreated DESC
Even if the generated date is not used in the SELECT clause, it must still be in the GROUP BY clause.
Hope it helps.
a source to share