Solved

complex mysql query

Posted on 2007-11-27
7
1,118 Views
Last Modified: 2012-08-13
I am trying to come up with a solution for my query below. heres my problem. the query below is on two tables. the second table "orders_products" can have multiple entries tied to the first table "orders". What I want to do is have a result that counts the sum of the entries in "orders_products" based on an extra id and returns those counts per row. For example.

orders contain the order_id and purchase date, and orders_products contain the products that were purchased with columns like order_id and product_id. I am trying to create a query that will report all products purchased within a timeframe and if any of the "product_id" match, count and combine those products into results per row. So my end result should look something like

row1 = sum of product_id1 - product_name1
row2 = sum of product_id2 - product name2.

heres what I have so far which basically returns every order within the date range.


$query = "SELECT o.*, op.*
FROM `orders` o, `orders_products op
WHERE o.orders_id = op.orders_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07'";
                                                                              
$data = mysql_query($query);
$rows = 0;
 while ($record = mysql_fetch_array($data) ) {
  $rows++;
  $records[] = $record;
}
0
Comment
Question by:redcable
[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
7 Comments
 
LVL 11

Expert Comment

by:elfe69
ID: 20358475
You should perform a GROUP BY:

SELECT o.orders_id, o.date_purchased, SUM(op.orders_products_id) AS products_count
FROM `orders` o, `orders_products op
WHERE o.orders_id = op.orders_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07
GROUP BY o.orders_id, o.date_purchased

You can see the GROUP BY reference here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20361633

$query = "
SELECT op.product_id, count(product_id) as CountSold, p.product_name
FROM `orders` o, `orders_products` op, products p
WHERE o.orders_id = op.orders_id
AND p.product_id = op.product_id
AND date_purchased >= '2007-11-06'
AND date_purchased <= '2007-11-07'
group by op.product_id";

Open in new window

0
 

Author Comment

by:redcable
ID: 20361810
end up with this, i think the above would work as well so accepting.

SELECT products_id, count(products_id)
FROM orders_products
JOIN orders on (orders_products.orders_id = orders.orders_id)
WHERE orders.date_purchased between '2007-11-01' and '2007-11-07'
GROUP BY orders_products.products_id
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 25

Expert Comment

by:imitchie
ID: 20362552
the above DOESN't work.

SUM(op.orders_products_id) AS products_count

gives you ID+ID+ID+ID instead of counting them.. fffff
0
 

Author Comment

by:redcable
ID: 20362648
oops imitchie i meant to accept yours, anyway t change or to late?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20363134
gracias
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

726 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