SQL query for sales summary.

Question

hi all, 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...................

Answer 1

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.

Answer 2

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

Answer 3

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.