473,326 Members | 2,680 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Need help to perform this action in sql.. see description inside.

15
Hello there,
here is the scenario.
i have 3 tables, like the following

Expand|Select|Wrap|Line Numbers
  1. [TABLE 1: PRODUCTS]
  2. [CODPROD],[ NAME]
  3. 1                 , PROD1
  4. 2                 , PROD2
  5. 3                 , PROD3
  6. [END OF TABLE 1]
  7.  
  8. [TABLE 2: SALES_X]
  9. [CODPROD], [AMT]
  10. 1                 , 3
  11. 2                 , 1
  12. 3                 , 1
  13. 2                 , 5
  14. 2                 , 1
  15. 1                 , 4
  16. [END OF TABLE 2]
  17.  
  18. [TABLE 3: SALES_Y]
  19. [CODPROD], [AMT]
  20. 3                 , 3
  21. 2                 , 1
  22. 2                 , 1
  23. 2                 , 1
  24. 1                 , 30
  25. [END OF TABLE 3]
  26.  
  27.  
What i'm trying to do and cannot figure out how to.

Expand|Select|Wrap|Line Numbers
  1. sql result:
  2. [PRODUCTS.NAME],[SALES_X.AMT + SALES_Y.AMT]
  3. PROD1                   , 37
  4. PROD2                   , 10                              
  5. PROD3                   , 4
  6.  

I Have tried using SUM(), COUNT(), Joins, Unions, but still did not get how to do it right..

i'm trying with this, but its bugged
Expand|Select|Wrap|Line Numbers
  1. select a.name, SUM(b.amt) + SUM(c.amt) from PRODUCTS a
  2. LEFT JOIN SALES_X      b
  3. ON b.codprod= a.codprod
  4. LEFT JOIN SALES_Y   c
  5. on c.codprod= a.codprod
  6. GROUP BY A.NAME
  7.  
i did manage a way for it on the application side, but it would be great to have a server-side solution for it...
Apr 30 '10 #1
2 1494
Atli
5,058 Expert 4TB
Hey.

Using a JOIN in that manner is problematic. The effect of adding two identical tables, who are essentially the same table split in two, can lead to data being repeated to compensate for the mismatch in row count between tables. - Basically, you can end up repeating rows from either table, making the count inaccurate.

I suggest, rather than trying to use a JOIN, that you try using Correlated Subqueries. That is; subqueries that reference tables from the outer query.

Basically, rather than JOIN the tables, you simply fetch the SUM from the sales tables using a subquery. You then use the "codprod" key from the PRODUCTS table to filter the result set used by the subqueries, by having the subqueries reference it's parent query.

Like:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     a.name,
  3.     (   SELECT SUM(x.amt)
  4.         FROM sales_x AS x
  5.         WHERE x.codprod = a.codprod
  6.     ) + (
  7.         SELECT SUM(y.amt)
  8.         FROM sales_y AS y
  9.         WHERE y.codprod = a.codprod
  10.     ) AS 'total'
  11. FROM products AS a
That should give you an accurate count.


But as I said above, the two sales tables are essentially just two parts of a larger table. Therefore, you could use a UNION to construct the complete table, and use a JOIN on that.
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     a.name,
  3.     SUM(s.amt)
  4. FROM products AS a
  5. LEFT JOIN (
  6.     SELECT * FROM sales_x
  7.     UNION ALL
  8.     SELECT * FROM sales_y
  9. ) AS s
  10.     ON s.codprod = a.codprod
  11. GROUP BY a.name
However this is likely to be far less efficient than the subquery version, as this requires MySQL to temporarily construct the "sales" table, and it uses a GROUP BY clause.
Apr 30 '10 #2
nexusbr
15
Wow, excellent!!!

i just had a little trouble with NULL values, but it's fixed with a CASE WHEN clause..

and i'm using the first solution.

thank you very much brother.
Apr 30 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Will | last post by:
(My 4 questins at end after explination) The code below was provided to me to "Popup" a window explaining what a Credit Card Verification Number is and where to find it on a card... it is used as...
3
by: CoX | last post by:
I am currently doing an assignment for my scripting module at univercity. The project is to create an auction site (I've just based mine on ebay) in asp.net using vb. One of the "extra mark"...
6
by: A.M | last post by:
Hi, Using ADO.NET, How can i execute action queries already saved inside an MDB file ? I already know that i can run sql commands like UPDATE and INSERT, but how can i run those ones already...
1
by: AutoShutdown | last post by:
I use vbscript in an asp file. How can i take the last string in a variable. For example: abc="hello123" newabc=???(abc) and i only want the last string, that is "3". means, if I do...
2
by: Dave | last post by:
I am looking to see if there is an example or pseudo code for how to have a C# application read a text file and perform an action that is parsed from the text file. For example TestDoc.txt...
0
by: AmyHanson | last post by:
I am new to mobile development and am having some trouble sources for the tasks I need to perform. I have been looking around and I can find plenty of information on copying the file initiating on...
0
by: ckkwan | last post by:
We know we need to put inside the <formelement, because the ASP control needs postback. If a particular ASP control doesn't need postback, it doesn't really need to be inside the <formelement. ...
23
by: irishalanm | last post by:
Hi, I am an ICT Student and I am designing a work related project which is using MS Access 2007 to create a database to monitor leave for fire brigade staff. I have studied relational database...
1
by: rupeshjain9406 | last post by:
Can someone tell me wat will have better scope -developing software for mobiles or developing desktop applications in .net.What will be the pay of a guy who has experience in developing software...
0
by: umeed4u | last post by:
i am student presently doing one academic project,but i came across with a problem which is as.. 1)my project contains coding in java swing,servlets where i wana to access it through mobile ... ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.