• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL question: how to solve this query with 2 tables? (image included)

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
In the table below you can see 2 tables; my goal is to SUM the production amount of every ID from Table_A BUT! if the ID has a branch_ID I should SUM their production as well.

For example, for ID 305 there is no branch so the sum will be 62 (30+32)

For ID 300 the sum should be 80+50 and to add 22 AND 23 (because 450 and 451 are branches of 300)

THANKS FOR ANY TIPS!!!



 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter,
I like to think about more complex queries as simpler queries. That gets me to think about them from the inside out.

Do you know how to:
1) create a select returning the id in two columns - id and 'id for sum'
2) create a select returning the id and branch - branch and 'id for sum'
3) union the two and sum them up

I've seen you around enough to know you can do most or all of these. The main purpose of the simple questions is to show my thought process.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok Jeanna,

Thanks for the tip - I got the idea; I'll try that and get back with result (or questions :-) )

thank you!
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Solved!


so I followed your instructions and I got the answer


SELECT TMP.ID, SUM(production) AS TOTAL
FROM table_a AS A,

(
SELECT B.home_ID AS ID, B.branch_id AS ID_FOR_SUM
from table_b AS B

union

SELECT DISTINCT A.ID, A.ID AS ID_FOR_SUM
FROM table_a AS A
WHERE A.ID NOT IN

(
SELECT DISTINCT A.ID
FROM table_a AS A, table_b AS B
WHERE A.ID = B.branch_ID
)
) AS TMP

WHERE A.ID = TMP.ID_FOR_SUM

GROUP BY TMP.ID




Thanks Jeanne!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good to hear you got it working. Thanks for sharing the final complex query. While you certainly know enough to translate my post into code, others seeing this thread in the future may not.
 
Gian Franco
blacksmith
Ranch Hand
Posts: 979
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Peter,

My fingers where itching to try another query

...what do think of the following query...

SELECT root.ID, root.total + NVL(branch.total, 0)
FROM (SELECT ID AS ID, SUM (production) AS total
FROM table_a
GROUP BY ID) root,
(SELECT home_id AS ID, SUM (production) AS total
FROM table_a, table_b
WHERE ID = branch_id
GROUP BY home_id) branch
WHERE root.ID = branch.ID(+)

Kind regards,

Gian
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Gian,

I'm using mySql so I can't use the function NVL.
 
Gian Franco
blacksmith
Ranch Hand
Posts: 979
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter Primrose wrote:
I'm using mySql so I can't use the function NVL.


Ok...anyway you could use ifNULL() if I'm not mistaken...
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use IFNULL in mysql.
edit: ah. I'm second!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic