SUM行，返回计数和按日期排序

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?).

抱歉坚持这个查询,因为我在order by子句中有数据它告诉我我必须放入聚合或group by子句? (即使我不需要那个总值?)。

Table UserData ( userID, sales, credits, dateCreated)

表UserData(userID,sales,credits,dateCreated)

My query has to return the last 10 results:

我的查询必须返回最后10个结果:

SELECT TOP 10 COUNT(*) as totalDays, SUM(sales), SUM(credits) FROM UserData WHERE userID = @userID ORDER BY dateCreated DESC

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

我有总天数,因为它可能不会返回我要求的天数(在这种情况下它是10,但可以在以后更改)。

3 个解决方案

#1

This gives you the totals for the last 10 days:

这将为您提供过去10天的总数:

SELECT
    COUNT(*) as totalDays, SUM(sales), SUM(credits) 
FROM 
    UserData 
WHERE 
    userID = @userID 
    AND DateCreated > GETDATE() - 10

Last 10 sales

#2

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.

对于仅返回结果中的单个行的查询,使用top或order by是没有意义的。首先,您必须使查询返回多行才能使用它们。

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

#3

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:

这是因为*(ALL)字段而发生的。由于你聚合了你应该选择另一个字段来计算,任何字段都会在你的情况下做,这将使你的查询像这样:

SELECT TOP 10 COUNT(USERID) AS TOTALDAYS, SUM(SALES), SUM(CREDITS) FROM USERDATA WHERE userid = @userid GROUP BY datecreated ORDER BY datecreated DESC

SELECT TOP 10 COUNT(USERID)作为TOTALDAYS,SUM(SALES),SUM(CREDITS)来自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.

即使在SELECT子句中未使用datecreated,它仍然需要在GROUP BY部分中。

Hope this helps.

希望这可以帮助。

#1