Jump to content

Archived

This topic is now archived and is closed to further replies.

kramer65

Very complex query with many joins and sums and multiplies..

Recommended Posts

Hello,

I worked the past weeks on a system of which the queries got progresively complex. Now i need one which is really beyond my knowledge.

I have a DB with 5 tables like this:

styles:
style_nr|rate
-------------
style1  | 25
style2  | 30
style3  | 35
style4  | 30

orders:
order_nr|style_nr|quantity
--------------------------
order1  |style2  | 50
order2  |style2  | 30
order3  |style3  | 25

issues:
issue_id|order_nr|fabricator_id|quantity_issued
--------------------------------------------
3      |order2  | 2          | 10
4      |order2  | 2          | 10
5      |order2  | 4          | 5
6      |order3  | 3          | 15

payments:
payment_id|fabricator_id|paid
-----------------------------
4        | 1          | 100
5        | 1          | 80
6        | 3          | 130
7        | 4          | 25

fabricators:
fabricator_id|name
------------------
  1        | Piet
  2        | Heijn
  3        | kees
  4        | Jan

WHERE styles.style_nr = orders.style_nr
AND orders.order_nr = issues.order_nr
AND issues.fabricator_id = payments.fabricator_id
AND payments.fabricator_id = fabricators.fabricator_id

Now I want to make a list which looks something like this:

Fabricator|total value issues|Amount paid
-----------------------------------------
Piet    | 4000            | 3900
Heijn    | 4560            | 5000
kees    | 440              | 450
Jan      | 40              | 45

So 'total value issues' should be issues.quantity_issued * styles.rate where the rate can be taken with help from the orders table. And then i need to do that for every seperate fabricator.
And 'Amount paid' should be the sum of payments.paid for the respective fabricator.

I do know how i could get the SUM(payments.paid) for 1 fabricator. But how i would be able to get the sum of all issues.quantity_issues * styles.rate per fabricator... no idea. And how i can get both of these into 1 query...? even less ideas...

Is there maybe someone so smart that he can solve this problem? (I actually even doubt whether it is possible at all..)

Share this post


Link to post
Share on other sites
[quote author=kramer65 link=topic=111890.msg453733#msg453733 date=1161176544]
Now I want to make a list which looks something like this:

Fabricator|total value issues|Amount paid
-----------------------------------------
Piet    | 4000            | 3900
Heijn    | 4560            | 5000
kees    | 440              | 450
Jan      | 40              | 45

So 'total value issues' should be issues.quantity_issued * styles.rate where the rate can be taken with help from the orders table. And then i need to do that for every seperate fabricator.
And 'Amount paid' should be the sum of payments.paid for the respective fabricator.

I do know how i could get the SUM(payments.paid) for 1 fabricator. But how i would be able to get the sum of all issues.quantity_issues * styles.rate per fabricator... no idea. And how i can get both of these into 1 query...? even less ideas...
[/quote]

This assumes each order has only one style_nr
[code]
SELECT
t1.*, IFNULL(SUM(p.paid), 0) AS paid
FROM
(
    SELECT
    f.fabricator_id, f.name, IFNULL(SUM(i.quantity_issued * s.rate), 0) AS issued
    FROM
    fabricators AS f
    LEFT JOIN
    issued AS i
    ON
    f.fabricator_id = i.fabricator_id
    LEFT JOIN
    orders AS o
    ON
    i.order_nr = o.order_nr
    LEFT JOIN
    styles AS s
    ON
    o.style_nr = s.style_nr
    GROUP BY
    f.fabricator_id
) AS t1
LEFT JOIN
payments AS p
ON
t1.fabricator_id = p.fabricator_id
GROUP BY
t1.fabricator_id
[/code]

[url=http://dev.mysql.com/doc/refman/4.1/en/subqueries.html]Subqueries[/url]
[url=http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html]Subqueries in FROM clause[/url]

Share this post


Link to post
Share on other sites
That is fantastic!! It works!

Really. This is beyond my knowledge... Although it works now i do not really understand the whole thing yet. I will start reading the links you gave me. Thanks a lot for that as well!

Share this post


Link to post
Share on other sites
Indeed it does... shoz has a gift for composing complex queries like this.  Out of curiousity, though, doesn't SUM() automatically handle NULLs?

Share this post


Link to post
Share on other sites
[quote=fenway]
shoz has a gift for composing complex queries like this
[/quote]

Thanks for the compliment.

[quote=fenway]
Out of curiousity, though, doesn't SUM() automatically handle NULLs?
[/quote]

It basically does. NULLs seem to be treated as 0 values if any non NULL values are part of the sum but if given only NULL values the result will be NULL. This'll be the case for a fabricator that doesn't have any payments and or doesn't have any issues.

Share this post


Link to post
Share on other sites
Of course.... now I remember.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.