SUM rows, return count and order by date

Question

Hi,

Sorry stuck on this query, since I have the data in the order by clause it tells me I have to put in an aggregate or group by clause? (even if I don't need that aggregate value?).

Table UserData ( userID, sales, credits, dateCreated)

My query has to 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 maybe it won't return the # of days I asked for (in this case it is 10, but it can be changed later).

Answer 1

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

Answer 2

This is happening because of the * (ALL) field. Since you aggregating you should pick another field to count, any field would do in your case which would 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 though the datecreated isn't used in the SELECT clause, it still needs to be in the GROUP BY portion.

Hope this helps.

Answer 3

There is no point in using top or order by on a query that only returns a single row in the result. First you have to make the query return more than one row to make any use of them.

This will simply aggregate all sales and return a single row, so you have to do something to it first:

select count(*) as totalDays, sum(sales), sum(credits)
from UserData
where userID = @userID

If you want to take the last ten sales and sum up, you need a subquery that first isolates the ten sales, then you can aggregate them:

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 up each day an return the last ten days, you need to group on the date:

select top 10 count(*) as totalDays, sum(sales), sum(credits)
from UserData
where userID = @userID
group by dateCreated
order by dateCreated desc