• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

SQL Query - need to Group by product to avoid numerous lines with the same product

I have this query:

with cte_1 as (
SELECT  product.prodcode, product.producttitle,
saleitem.quantity, productprice.retailprice, saleitem.quantity*productprice.retailprice as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle,
saleitem.quantity, productprice.retailprice
)
select * from cte_1 union all
select null,null,null,null,sum(itemtotal) from cte_1

It works fairly well but some products repeat numerous times, so my quetions is if it is possible to group these together and sum up the quantities in order to have a report of products sold at their actual prices and sum up their quantity. I have included a textfile with query results in a txt file. I understand that perhaps the same products but with a different price won't be able to merge but I think there are records of the same product with the same price but different quantity.. repeating itself.

Thanks a lot again guys, all your help is much appreciated

query-result.txt
0
czechmate1976
Asked:
czechmate1976
  • 5
  • 3
  • 3
  • +2
2 Solutions
 
shru_0409Commented:
b'coz of price is different . either y have to take max price or min price
i.e min(productprice.retailprice)  or  Max(productprice.retailprice)

SELECT  product.prodcode, product.producttitle,
sum(saleitem.quantity), max(productprice.retailprice), sum(saleitem.quantity) *max(productprice.retailprice) as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle

0
 
js-profiCommented:
Try

GROUP BY productprice.PRODCODE, productprice.retailprice

0
 
czechmate1976Author Commented:
Thank you for your help it seems to give a correct total.. (well, if my previous one was correct)

But does this take into account that the products had a different price at different points in time? Doesn't it actually only selects a product with its max price and calculates everything with a single price dispite it being sold at different prices? I could be wrong though, that's why I am asking :-)
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
czechmate1976Author Commented:
I think this little adjustment has done it. I have taken away the max from price, it does duplicate some items but only those with different prices.. and the total is correct.

Do you think it is really correct?
with cte_1 as (
SELECT  product.prodcode, product.producttitle,
sum(saleitem.quantity), productprice.retailprice, sum(saleitem.quantity) *productprice.retailprice as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
group by product.prodcode, product.producttitle, productprice.retailprice
ORDER BY product.prodcode
)
select * from cte_1 union all
select null,null,null,null,sum(itemtotal) from cte_1

Open in new window

0
 
shru_0409Commented:
in your data same product has two different price. if u r not taken min or may as per your requirement than it will come two rows...
try it
0
 
js-profiCommented:
the group by on productprice will group by products and each individual price stored in productprice table. it should work if any new price for a product will lead to a new entry in productprice. It even works if the same price for a product exists twice or more in productprice.
0
 
SharathData EngineerCommented:
check this.
with cte_1 as (
SELECT  product.prodcode, product.producttitle,
saleitem.quantity, productprice.retailprice, saleitem.quantity*productprice.retailprice as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle,
saleitem.quantity, productprice.retailprice
),
cte_2 as (
select prodcode,producttitle,sum(quantity) total_quantity,min(retailprice) min_retailprice, max(retailprice) max_retailprice,
       sum(itemtotal) sum_itemtotal
 from cte_1
 group by prodcode,producttitle)
select * from cte_2 union all
select null,null,null,null,sum(itemtotal) from cte_2

Open in new window

0
 
czechmate1976Author Commented:
Tried it and it gives this error:

Error at Command Line:20 Column:31
Error report:
SQL Error: ORA-00904: "ITEMTOTAL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
0
 
jamesguCommented:
czechmate1976,

your solution seems good to me
0
 
SharathData EngineerCommented:
Is this the error message with my query? I guess you missed SUM function in your original query. can you try this?

with cte_1 as (
SELECT  product.prodcode, product.producttitle,
saleitem.quantity, productprice.retailprice, sum(saleitem.quantity*productprice.retailprice) as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle,
saleitem.quantity, productprice.retailprice
),
cte_2 as (
select prodcode,producttitle,sum(quantity) total_quantity,min(retailprice) min_retailprice, max(retailprice) max_retailprice,
       sum(itemtotal) sum_itemtotal
 from cte_1
 group by prodcode,producttitle)
select * from cte_2 union all
select null,null,null,null,sum(itemtotal) from cte_2
0
 
czechmate1976Author Commented:
yep... I tried your query Sharath and got this error (that I stated above). Will try this one now and let you know :-)
0
 
czechmate1976Author Commented:
I think it is the same error.. :-(

Error at Command Line:20 Column:31
Error report:
SQL Error: ORA-00904: "ITEMTOTAL": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
0
 
SharathData EngineerCommented:
my bad. try this.

with cte_1 as (
SELECT  product.prodcode, product.producttitle,
saleitem.quantity, productprice.retailprice, sum(saleitem.quantity*productprice.retailprice) as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle,
saleitem.quantity, productprice.retailprice
),
cte_2 as (
select prodcode,producttitle,sum(quantity) total_quantity,min(retailprice) min_retailprice, max(retailprice) max_retailprice,
      sum(itemtotal) sum_itemtotal
from cte_1
group by prodcode,producttitle)
select * from cte_2 union all
select null,null,null,null,null,sum(sum_itemtotal) from cte_2
0
 
shru_0409Commented:
with cte_1 as (
SELECT  product.prodcode, product.producttitle,
saleitem.quantity, productprice.retailprice, sum(saleitem.quantity*productprice.retailprice) as itemtotal
FROM customer, saleitem, customersale, product, productprice
WHERE customer.custid = customersale.custid
AND customersale.salref = saleitem.salref
AND saleitem.prodcode = product.prodcode
AND product.prodcode = productprice.prodcode
AND productprice.startdate <= customersale.saledate
AND customersale.saledate <= nvl(productprice.enddate, '03-JAN-2010')  
GROUP BY product.PRODCODE, product.producttitle,
saleitem.quantity, productprice.retailprice
),
cte_2 as (
select prodcode,producttitle,sum(quantity) total_quantity,min(retailprice) min_retailprice, max(retailprice) max_retailprice,
      sum(min(retailprice)* max(retailprice) ) sum_itemtotal
from cte_1
group by prodcode,producttitle)
select * from cte_2 union all
select null,null,null,null,null,sum(sum_itemtotal) from cte_2

u try this...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now