Forum Moderators: open
I've got a database with three tables that contain purchase order information, and I want to display the order info in a table with order quantities for both "all-time" and "month-to-date" in order of "Zone" (that's my GROUP BY). I came up with two separate queries that pull this information, but I'm having trouble displaying it all in one table the way I want to. This is my desired output:
Zone ID.....Qty MTD.....Qty All TimeAnd so on...displaying zeroes in the MTD column if there haven't been any orders for that zone so far this month, but still showing the all-time total for that zone in the All Time column.
1...........3...........5
3...........0...........10
4...........0...........22
6...........5...........5
8...........3...........12
9...........0...........15
My tables are:
orgs (containing "org_id" and "zone_id" columns);
orders (containing "orders_id" "org_id" and "date" columns); and
orders_details (containing "orders_details_id" "orders_id" "quantity" and "value" columns).
I started out with this query for "all time" totals:
select sum(orders_details.quantity * orders_details.value) as value, sum(orders_details.quantity) as quantity, orgs.zone_id from (orgs INNER JOIN orders ON orgs.org_id = orders.org_id) INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id GROUP BY orgs.zone_idwhich works; and this query for MTD totals only:
select sum(orders_details.quantity * orders_details.value) as value, sum(orders_details.quantity) as quantity, orgs.zone_id from (orgs INNER JOIN orders ON orgs.org_id = orders.org_id) INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id WHERE (orders.date >= DATE_SUB(CURDATE(), INTERVAL (DAYOFMONTH(CURDATE())) DAY)) GROUP BY orgs.zone_idwhich, by itself, also works okay. Then I had a while loop set up to display everything in a table:
if ($result_all && mysql_num_rows($result_all) && $result_mtd && mysql_num_rows($result_mtd)) {echo '<TABLE>
<TR>
<TH> </TH>
<TH>Quantity <BR>MTD</TH>
<TH>Quantity <BR>(All-Time)</TH>
<TH>$$$ <BR>MTD</TH>
<TH>$$$ <BR>All-Time</TH>
</TR>';
while (($row_mtd = mysql_fetch_array($result_mtd)) && ($row_all = mysql_fetch_array($result_all))) {
echo '<TR><TD>Zone '.$row_mtd['zone_id'].'</TD>';
echo '<TD>'.$row_mtd['quantity'].'</TD>';
echo '<TD>'.$row_all['quantity'].'</TD>';
printf('<TD>$%.2f', $row_mtd['value']).'</TD>';
printf('<TD>$%.2f', $row_all['value']).'</TD></TR>';
$qty_mtd = ($qty_mtd + $row_mtd['quantity']);
$funds_mtd = ($funds_mtd + $row_mtd['value']);
$qty_all = ($qty_all + $row_all['quantity']);
$funds_all = ($funds_all + $row_all['value']);
}
echo '<TR><TD><B>Totals:</B></TD>';
echo '<TD><B>'.$qty_mtd.'</B></TD>';
echo '<TD><B>'.$qty_all.'</B></TD>';
printf('<TD><B>$%.2f</B></TD>', $funds_mtd);
printf('<TD><B>$%.2f</B></TD>', $funds_all);
echo '</TR></TABLE><P>';
}
else {
echo 'Sorry, there are no results.';
}
I tried doing a simple UNION on the two queries, but that ends up displaying the table in a very weird way -- the totals from the MTD query are sort of appended to the end of the table, instead of being merged into the table with the "all time" data.
I have a feeling that I need some sort of LEFT JOIN somewhere in here, so that the null values will be displayed where they need to be, and/or a UNION on top of it, but I'm at a loss for what to do next. Any pointers? I am kind of flying blind with UNIONS and JOINS but I have tried to search diligently before asking.
select sum(orders_details.quantity * orders_details.value) as value,
sum(orders_details.quantity) as qty_alltime,
(select sum(orders_details.quantity)
from (orgs as orgs2
INNER JOIN orders as orders2
ON orgs2.org_id = orders2.org_id)
INNER JOIN orders_details as order_details2
ON orders2.orders_id = orders_details2.orders_id
WHERE (orgs2.zone_id = orgs2.zone_id AND
orders2.date >= DATE_SUB(CURDATE(),
INTERVAL (DAYOFMONTH(CURDATE())) DAY))) as qty_mtd,
orgs.zone_id
from (orgs INNER JOIN orders ON orgs.org_id = orders.org_id) INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id
GROUP BY orgs.zone_id
It's probably my fault in that I didn't fully describe what I was trying to do in my original post -- trying to get quantities and order totals (value) for my table, though you probably gathered that from the rest of my post.
If it helps, the output I'm actually going for is more like this:
Zone ID...Qty MTD...Total $ MTD...Qty All Time...Total $ All TimeShould the joins be LEFT JOINS (or RIGHT) in order to make sure the null values I need are being pulled? I also am not sure why "orgs2.zone_id = orgs2.zone_id" would need to be in the WHERE clause.
1.........3...............15.00..............5..............25.00
3.........0...................0.............10..............40.25
4.........0...................0.............22.............127.50
6.........5...............22.00..............5..............22.00
8.........3...............15.00.............12..............65.25
9.........0...................0.............15..............84.75
WHERE (orgs2.zone_id = orgs.zone_id AND
2. I removed unnecessary parenthesis to simplify:
(select sum(orders_details.quantity)
from orgs as orgs2
INNER JOIN orders as orders2
ON orgs2.org_id = orders2.org_id
INNER JOIN orders_details as order_details2
ON orders2.orders_id = orders_details2.orders_id
WHERE orgs2.zone_id = orgs.zone_id AND
orders2.date >= DATE_SUB(CURDATE(),
INTERVAL (DAYOFMONTH(CURDATE()) DAY))
)
3.Does your version of mySQL allow sub queries?
4. Does the INTERVAL function need a comma?
5. I don't think LEFT JOINS are a factor here as long as your data is relational.
SELECT sum( orders_details.quantity * orders_details.value ) AS value, sum( orders_details.quantity ) AS qty_alltime, (
SELECT sum( orders_details.quantity )
FROM orgs AS orgs2
INNER JOIN orders AS orders2 ON orgs2.org_id = orders2.org_id
INNER JOIN orders_details AS orders_details2 ON orders2.orders_id = orders_details2.orders_id
WHERE orgs2.zone_id = orgs.zone_id
AND orders2.date >= DATE_SUB( CURDATE( ) , INTERVAL( DAYOFMONTH( CURDATE( ) ) )
DAY )
) AS qty_mtd, orgs.zone_id
FROM (
orgs
INNER JOIN orders ON orgs.org_id = orders.org_id
)
INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id
GROUP BY orgs.zone_id
at least no longer throws an error. However, it's displaying the same amount in "qty_mtd" as "qty_alltime" and I'm missing a column for "value_mtd" -- I tried to add that to the subquery, but it gave me a "#1241 - Operand should contain 1 column(s)" error. This is what I had tried:
SELECT sum( orders_details.quantity * orders_details.value ) AS value, sum( orders_details.quantity ) AS qty_alltime, (
SELECT sum( orders_details.quantity * orders_details.value ) , sum( orders_details.quantity )
FROM orgs AS orgs2
INNER JOIN orders AS orders2 ON orgs2.org_id = orders2.org_id
INNER JOIN orders_details AS orders_details2 ON orders2.orders_id = orders_details2.orders_id
WHERE orgs2.zone_id = orgs.zone_id
AND orders2.date & gt ; = DATE_SUB( CURDATE( ) , INTERVAL( DAYOFMONTH( CURDATE( ) ) )
DAY )
) AS value_mtd, qty_mtd, orgs.zone_id
FROM (
orgs
INNER JOIN orders ON orgs.org_id = orders.org_id
)
INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id
GROUP BY orgs.zone_id
Not sure why it's not splitting out the MTD numbers at this point, that WHERE clause works fine when it's by itself...Is it because we're not renaming them in the subquery? (Like I said, flying a little blind here.)
I'm also still not sure why we're putting that "orgs2.zone_id = orgs.zone_id" in the WHERE clause, is that needed for the subquery for some reason?
And yes, I am on MySQL 5.0 for this db. The documentation seems to indicate that a comma is needed with INTERVAL.
It is necessary to have orgs2.zone_id=orgs.zone_id in the subquery to make it a "correlated subquery". A correlated subquery forces a recalculation for each row in the outer query. Otherwise, it would be the MTD value for the whole table.
I suspect the DATE_SUB function is causing the MTD value to be the same as the All-time value.
Weird, the DATE_SUB function works fine by itself, like if I just run it on the "orders" table by itself:
SELECT *
FROM `orders`
WHERE date >= DATE_SUB( CURDATE( ) , INTERVAL( DAYOFMONTH( CURDATE( ) ) )
DAY )
~~~
SELECT SUM(orders_details.products_quantity) as quantity, SUM(orders_details.products_quantity * orders_details.value) as value, (
SELECT SUM(orders_details2.products_quantity)
FROM orgs AS orgs2
LEFT JOIN orders AS orders2 ON orgs2.org_id = orders2.org_id
INNER JOIN orders_details AS orders_details2 ON orders2.orders_id = orders_details2.orders_id
WHERE orgs.zone_id = orgs2.zone_id
AND orders2.date >= DATE_SUB( CURDATE( ) , INTERVAL( DAYOFMONTH( CURDATE( ) ) ) DAY )
) AS quantity_mtd, (
SELECT SUM(orders_details2.products_quantity * orders_details2.value)
FROM orgs AS orgs2
LEFT JOIN orders AS orders2 ON orgs2.org_id = orders2.org_id
INNER JOIN orders_details AS orders_details2 ON orders2.orders_id = orders_details2.orders_id
WHERE orgs.zone_id = orgs2.zone_id
AND orders2.date >= DATE_SUB( CURDATE( ) , INTERVAL( DAYOFMONTH( CURDATE( ) ) ) DAY )
) AS value_mtd, orgs.zone_id
FROM orgs
LEFT JOIN orders ON orgs.org_id = orders.org_id
INNER JOIN orders_details ON orders.orders_id = orders_details.orders_id
GROUP BY orgs.zone_id
~~~
It shows me the zone ID, all-time quantity, MTD quantity, all-time amount, MTD amount, and null values wherever they should be, like if there haven't been any orders for a particular zone this month. Near as I can tell, there was nothing wrong with the DATE_SUB function. Constant tweaking and fiddling with the table aliases and parentheses eventually paid off. Thanks very much for your help, syber!
