Hi,
I have two tables:
orders -
orderid int(11) [UNIQUE]
total decimal(12,2)
source varchar(64)
[...]
order_details -
orderid int(11)
amount int(11)
[...]
The 'orders' table holds basic information about the order (customer,
sales tax, etc.) and the 'order_details' table holds products ordered
(each product it's own row). 'amount' in the 'order_details' table is
the quantity of that particular product ordered. I'm trying to select
the total sales dollar amount, how many orders, how many products, and
how many line items for all orders which match a certain source (how the
customer placed the order).
I write a query like this thinking it will do the job...
SELECT
COUNT(orders.orderid) AS count,
SUM(orders.total) AS total,
COUNT(order_details.orderid) AS line_items,
SUM(order_details.amount) AS products
FROM
db.orders,
db.order_details
WHERE
orders.source = '<whatever source>' &&
orders.orderid = order_details.orderid
However, the results are not what I was looking for, or expecting...
count => 62
total => 7821.68
line_items => 62
products => 74
Doing a physical inspection of the data shows that line_items and
products are correct, but count is off (it seems to be putting in the
count of orderid from the order_details table and not the orders table),
and total is off (it seems to be giving me the sum of total in the
orders table each time there is a matching orderid in the order_details
table). When I just do the select on the orders table...
SELECT
COUNT(orders.orderid) AS count,
SUM(orders.total) AS total
FROM
db.orders
WHERE
orders.source = '<whatever source>'
I get the correct values for count and total...
count => 28
total => 2248.42
I /could/ just break this up into multiple queries, but I'd like to keep
it to just one. Does anyone know why it's working this way? Everything
I've been reading leads me to believe that I'm doing it right (but that
doesn't mean I'm reading everything correct).
--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
