代码之家 › 专栏 › 技术社区 › Raj

销售汇总SQL查询

tsql

Raj · 技术社区 · 15 年前

select pName,Price, (Price*Quantity) as SalesValues from saleslog where 
BillDate='12-10-2010' and pGroup=15 group by pname, Quantity, Price;

此查询旨在根据日期显示销售摘要,但它显示的是重复的产品名称。

MAK 2 T OIL 5LTR.   635    3175
MAK 2 T OIL 5LTR.   635    6350
MAK ELITE 3LTR     422    6330
MAK ELITE 3LTR     422    8440
SYSTEM PURGE         305    6100
SYSTEM PURGE         305    15250

谢谢。。。。。。。。。。。。。。。。。。。

3 回复 | 直到 15 年前

Quassnoi 15 年前

SELECT pName, SUM(Price * Quantity) AS SalesValues
FROM   saleslog
WHERE  BillDate = '12-10-2010' 
       AND pGroup = 15
GROUP BY
       pname

我搬走了 Price 因为它可以(可能)变化,而且不清楚输出哪一个。

如果价格不变,请使用:

SELECT pName, MIN(price), SUM(Price * Quantity) AS SalesValues
FROM   saleslog
WHERE  BillDate = '12-10-2010' 
       AND pGroup = 15
GROUP BY
       pname

Oded 15 年前

试试这个:

select pName, price, SUM(Price*Quantity) as SalesValues 
from saleslog 
where BillDate='12-10-2010' and pGroup=15 
group by pname, price

您只需要按select子句上不在聚合中的列进行分组。我假设每种产品都有相同的价格。

您甚至不必按价格分组,如果您使用聚合,请显示它(例如,最小值或最大值):

select pName, MAX(price), SUM(Price*Quantity) as SalesValues 
from saleslog 
where BillDate='12-10-2010' and pGroup=15 
group by pname

GROUP BY 条款:

按一个或多个列或表达式的值将选定的行集分组为一组摘要行。每组返回一行。SELECT子句列表中的聚合函数提供有关每个组的信息,而不是单个行的信息。

Jeffrey L Whitledge 15 年前

select pName,Price, (Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname, Quantity, Price;

现在你说你只想按pName分组。那我们就这么做吧。

select pName,Price, (Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

现在,当然,它会给出一个错误。所以我们需要对其他列进行聚合。对SalesValues列求和是有意义的。

select pName,Price, SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

不过,价格栏仍然是个问题。什么聚合器有意义?这要看情况。你可以做最大,最小,或平均,我猜。但实际上,它要么被忽略,要么被添加到组中。如果它被添加回group by,那么每个pName就不能再有一行了。如果您确实在Prince上放置了聚合器,那么请确保更改列的名称以反映它的含义。

/* Leave out the Price completely. (My favorite option.) */
select pName,SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

/* Group by Price. You now have multiple rows per pName. */
select pName,Price, SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname, Price;

/* Average the Price. (OK, but could lead to confusion.) */
select pName,AVG(Price) as AveragePrice, SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

/* Max Price. (Almost useless). */
select pName,MAX(Price) as MaximumPrice, SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

/* Min Price. (Almost useless). */
select pName,MIN(Price) as MinimumPrice, SUM(Price*Quantity) as SalesValues from saleslog where  
BillDate='12-10-2010' and pGroup=15 group by pname;

不过,我不建议对价格进行合计(尤其是最大值和最小值),因为当人们试图使用价值时,这可能会导致混乱。