?
Solved

Tricky Data Manipulation Question

Posted on 2006-06-19
5
Medium Priority
?
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 16939780
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
ID: 16939883
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
ID: 16939905
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
ID: 16939971
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 2000 total points
ID: 16940130
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

777 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