please help me with this query
select pName,Price, (Price*Quantity) as SalesValues from saleslog where
BillDate='12-10-2010' and pGroup=15 group by pname, Quantity, Price;
This query is intended to show sales summary according to date but it is showing duplicate Products Name.
Output of above query
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
as you can see repeated product name but i want sale summary according to Product Name.
thanks...................
SELECT pName, SUM(Price * Quantity) AS SalesValues
FROM saleslog
WHERE BillDate = '12-10-2010'
AND pGroup = 15
GROUP BY
pname
I removed Price
since it can (possibly) vary and it's not clear which one to output.
If the price does not vary, use this:
SELECT pName, MIN(price), SUM(Price * Quantity) AS SalesValues
FROM saleslog
WHERE BillDate = '12-10-2010'
AND pGroup = 15
GROUP BY
pname
Try this:
select pName, price, SUM(Price*Quantity) as SalesValues
from saleslog
where BillDate='12-10-2010' and pGroup=15
group by pname, price
You only need to group by columns that are not in an aggregate on the select clause. I am assuming that each product has an identical price.
You don't even have to group by price, if you use an aggregate do display it (MIN or MAX for example):
select pName, MAX(price), SUM(Price*Quantity) as SalesValues
from saleslog
where BillDate='12-10-2010' and pGroup=15
group by pname
Here is some documentation on the GROUP BY
clause:
Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions. One row is returned for each group. Aggregate functions in the SELECT clause list provide information about each group instead of individual rows.
Here's your original query:
select pName,Price, (Price*Quantity) as SalesValues from saleslog where
BillDate='12-10-2010' and pGroup=15 group by pname, Quantity, Price;
Now you say you want it grouped by pName only. So let's do that.
select pName,Price, (Price*Quantity) as SalesValues from saleslog where
BillDate='12-10-2010' and pGroup=15 group by pname;
Now, of course, it is going to give an error. So we need to put some aggregation on the other columns. It makes sense to SUM the SalesValues column.
select pName,Price, SUM(Price*Quantity) as SalesValues from saleslog where
BillDate='12-10-2010' and pGroup=15 group by pname;
The Price column is still a problem, though. What aggregator makes sense for that? It just depends. You could do MAX, MIN, or AVERAGE, I guess. But really it needs to be either left out, or added back to the group-by. If it's added back to the group by, then you can no longer have a single row for each pName. If you do put an aggregator on Prince, then be sure to change the name of the column to reflect what it means.
/* 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;
I don't recommend aggregating Price, though, (especially MAX and MIN) since it will likely lead to confusion down the line when people try to use the value.
精彩评论