Solved

Tricky Data Manipulation Question

Posted on 2006-06-19
5
182 Views
Last Modified: 2013-12-24
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
Comment
Question by:Westside2004
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
 
LVL 1

Author Comment

by:Westside2004
Comment Utility
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
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
 
LVL 1

Author Comment

by:Westside2004
Comment Utility
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
 
LVL 18

Accepted Solution

by:
Plucka earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now