SQL - make one record multiple records based on field value

[williamsba]

Here is my query:

SELECT o.orderid, o.po_id, ((SUM(op.quantity) / 3) / 4) as itemtotal, COUNT(*) as counter
FROM orders o
INNER JOIN order_products op ON rderID = op.OrderID
INNER JOIN applications a ON op.app_ID = a.app_id
INNER JOIN products p ON a.prodcode = p.prodcode
WHERE o.orderid = 'CC1098179'
GROUP BY o.orderid, o.po_id


Ok so this will return one record similar to this:

Order ID | PO ID | Item Total | Counter
343242 | 34234 | 4 | <NULL>


What I want to happen is return as many records as [Item Total] equals and count. So in this case it would return

Order ID | PO ID | Item Total | Counter
343242 | 34234 | 4 | 1
343242 | 34234 | 4 | 2
343242 | 34234 | 4 | 3
343242 | 34234 | 4 | 4


Possible or not?

Brad Williams
Webmaster

 

[donutman]

1) Explain the /3 and /4 (how do you know what to divide by)?
2) Explain "WHERE o.orderid = 'CC1098179'". Why does results not show that?
3) Is the po_id really in the Orders table? If so can one order ID have more than one po_id? If so what do you want done in that case?
4) Can there be a duplicate productcode for 1 OrderID?

Look through the FAQ's for this forum. There is a solution posted depending on the answers to the above.
-Karl

 

[williamsba]

1) That is what it has to be divided by to figure out my formula
2) It was just an example. The order ID will be whatever I type in
3) Every order ID has ONE po ID attached to it, never more than one
4) Yes there can be, but for the sake of simpleness I just want to figure out how to do this to one record, then I can get it to work with multiple records

Brad Williams
Webmaster

 

[Pattycake245]

I think the code below will work as long as you have a unique id in your orders table:

SELECT o.orderid, o.po_id, ((SUM(op.quantity) / 3) / 4) as itemtotal, (select count(*) from orders oo where
oo.orderid=orders.orderid and
oo.uniqueid < (orders.uniqueid)+1) counter
FROM orders o
INNER JOIN order_products op ON rderID = op.OrderID
INNER JOIN applications a ON op.app_ID = a.app_id
INNER JOIN products p ON a.prodcode = p.prodcode
WHERE o.orderid = 'CC1098179'
GROUP BY o.orderid, o.po_id

Tim

 

[donutman]

If there are no duplicate prodcodes for each orderid then I was shooting for the following way to create the "counter":
(select count(*) from orders oc
INNER JOIN order_products opc ON oc.OrderID = opc.OrderID
INNER JOIN applications ac ON opc.app_ID = ac.app_id
INNER JOIN products pc ON ac.prodcode = pc.prodcode
where ac.prodcode<=p.productcode and rderID=oc.OrderID) counter

I believe this will work but it will also count the duplicates, so the counter may go like this: 1 2 4 4 5.
That would indicate there was a duplicate in the third and fourth prodcode.

I don't think the simpler code for counter will work because the 1 to many relationship is coming from the applications table not the order table.
-Karl