Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Tricky Data Manipulation Question

Hi,

This is a tricky one I think, I am hoping it can be done in a way that is not too kludgy.

I have a query that returns a list of orders.  Each order can have multiple order items obviously.  Each order item has a "productNumber" and a "quantity" field in the recordset.

<cfloop query="orders">

    <cfquery datasource="#request.dsn#" name="get_order_items">
        SELECT *
        FROM order_items
       WHERE order_id = #orders.order_id#
    </cfquery>
 
<cfloop>

The trick is a given order which contains "order items"  can have the same "product number" multiple times.  I want to be able to associate the quantities with the product numbers.

There might be 2 orders in total, and say each order has 5 items.  

Order # 1 contains the following:

prod# | qty
006  - 5
004  - 2
002  - 9
001  - 4
094  - 3

Order # 2 contains the following:

prod# | qty
006  - 5
004  - 2
002  - 9
001  - 4
094  - 3

Ultimately I want to be able to have the result like this

006 |  004  | 002
10   |  4     | 18

Ultimately this is going to excel for some functionality, that is why I need to be able to build this output.  Immediately above you see the productNumbers with a total quanity for that given productNumber.  The end result is a grid with the product numbers as column headers, and the values associated with each product number for a given customer.

Hope that makes sense.

-ws
0
Westside2004
Asked:
Westside2004
  • 3
  • 2
1 Solution
 
PluckaCommented:
Westside2004,

You can do all this in a single query

select      order_id, prod, sum(qty) as qty
from        order_items
group by    order_id, prod

This will give you a query object with the following three fields

OrderId, Prod & Total Quantity

If you want this to summaries just products across the orders just change the query like


select      prod, sum(qty) as qty
from        order_items
group by    prod

This will output what you are asking for (assuming I understand what that is)

Product, Total Quantity across orders.

Regards
Plucka
0
 
Westside2004Author Commented:
Hi,

Now that you say that, I think you are most likely right, I am not sure what I was thinking.  In any case, the query is not quite giving me what I am looking for.

I am getting

For orderId # 1

qty | productNumber
1      006
1      009
1     095

For orderId # 2

qty | productNumber
1      006
1      009
1     095

Basically I am getting multiple productNumbers.  I should only get the combined totals across a given product number so if the product number is 006, and there have been 5 orders and in each order the customer ordered 3 of "productNumber 006" then I should see

qty  | productNumber
15     006

Does that make sense?  Even though multiple customers can order the same product, I do not want to get the productNumber multiple times, I just want the total qty orderered for that given productNumber.

-ws

0
 
PluckaCommented:
Yes,

You shouldn't need two queries, what is the point of the first query? then looping through for the second.

You should either

1. just use the query I gave you

or

2. If you need stuff from the first query, get it in a single query using inner joins.

If you show me the first query I could sort it out for you.
0
 
Westside2004Author Commented:
The first query gets all the orders for a given date range.

SELECT *
FROM ORDERS
WHERE ORDERID > -1
AND date between '06/01/06' and '06/05/06'

I need the orderid to use in the second query so when I am getting the order items I can pull them from the order items table by orderid.

Too bad I can't post a screen shot, that would be cool...

In any case, I am getting the order items for each given order, so for orderid 10 lets say, I see "x' amount of order items, for orderid 11, i see "x" amount of  order items.

Included in these order items is the same productNumber as the different customers chose the same product

So I should get this:

qty  | productNumber
15     006

not

For orderId # 1

qty | productNumber
8      006


For orderId # 2

qty | productNumber
7      006

Hope that helps clarify.

-ws
0
 
PluckaCommented:
SELECT order_items.prod as prod, sum(order_Items.qty) as qty
FROM ORDERS
inner join order_items on orders.orderId = order_items.orderId
WHERE ORDERID > -1
AND date between '06/01/06' and '06/05/06'
group by order_items.prod
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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