• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!!!



 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
blacksmith
Posts: 979
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Gian,

I'm using mySql so I can't use the function NVL.
 
Gian Franco
blacksmith
Posts: 979
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Use IFNULL in mysql.
edit: ah. I'm second!
 
Whip out those weird instruments of science and probe away! I think it's a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic