Solved

MYSQL: How to get different columns from different tables using one select statement

Posted on 2014-10-01
8
263 Views
Last Modified: 2014-10-02
I have two very similar tables (with identical column names), that I want to get results from using a single query.

My SQL is this

SELECT mbtd_yield_classic_round1.yldGrowerID, Sum(mbtd_yield_classic_round1.yldYield) AS SumYield1, Sum(mbtd_yield_classic_round2.yldYield) AS SumYield2
FROM mbtd_yield_classic_round1, mbtd_yield_classic_round2
GROUP BY mbtd_yield_classic_round1.yldGrowerID;

Open in new window


However as I am summing the Yield columns, the value returned are not what I expect. They should be:
A : 5.1 : 3.2
B : 3.7 : 4.0

instead I get

A : 51 : 36
A : 37 : 36

Can someone help me please to get the correct results from this simple query ?
0
Comment
Question by:Sean Clarke
  • 4
  • 2
  • 2
8 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Can you please provide some sample data?
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

You will need to summarize one table at a time and then combine those results into one
like this

SELECT t1.yldGrowerID, Sum(t1.yldYield) AS SumYield
FROM mbtd_yield_classic_round1 t1
GROUP BY t1.yldGrowerID;
UNION
SELECT t2.yldGrowerID, Sum(t2.yldYield) AS SumYield
FROM mbtd_yield_classic_round2 t2 
GROUP BY t2.yldGrowerID;

Open in new window


Regards,
     Tomas Helgi
0
 
LVL 1

Author Comment

by:Sean Clarke
Comment Utility
Hi, Sorry - I forgot to post my sample data.

Tomas, this UNION will only give me One Grower ID and ONE TOTAL SUM.
I Need two Totals - one from Table 1 and one from Table 2.
Table-1.PNG
Table-2.PNG
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
Comment Utility
Remove the GROUP By and the SUM and the problem will become apparent: you're using a Cartesian product, which means the resulting table will have 10X10 rows.

Try something like this:
SELECT t3.yldGrowerID, SUM(SumYield1), SUM(SumYield2)
FROM
(SELECT t1.yldGrowerID, Sum(t1.yldYield) AS SumYield1, '0' as SumYield2
FROM mtbd_yield_classic_round1 t1
GROUP BY t1.yldGrowerID
UNION
SELECT t2.yldGrowerID, '0' AS SumYield1, Sum(t2.yldYield) AS SumYield2
FROM mtbd_yield_classic_round2 t2 
GROUP BY t2.yldGrowerID) t3
GROUP BY t3.yldGrowerID

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
Hi!

Then if you use UNION ALL like this
SELECT t1.yldGrowerID, Sum(t1.yldYield) AS SumYield
FROM mbtd_yield_classic_round1 t1
GROUP BY t1.yldGrowerID;
UNION ALL
SELECT t2.yldGrowerID, Sum(t2.yldYield) AS SumYield
FROM mbtd_yield_classic_round2 t2 
GROUP BY t2.yldGrowerID;

Open in new window


You should get 2 rows and if they are identical then the sum and values for yldGrowerID are the same in both
tables.

Regards,
    Tomas Helgi
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
@Tomas: the OP wants the result to have 2 rows and 3 columns:

yldGrowerID  |  SumYield1  | SumYield2
     A                 |         5.1        |     3.2
     B                 |         3.7        |     4.0

Your query returns 4 rows and 2 columns.
0
 
LVL 1

Author Closing Comment

by:Sean Clarke
Comment Utility
Excellent thank for this - kind of obvious when I look at what you've done, but would have never worked it out on my own.

My final query ended up like this:

SELECT t3.yldGrowerID, ROUND(SUM(t3.SumYield1),2) AS SumYield1, ROUND(SUM(t3.SumYield2),2) AS SumYield2 FROM
(SELECT t1.yldGrowerID, SUM(t1.yldYield) AS SumYield1, '0' AS SumYield2 FROM mbtd_yield_classic_round1 t1 GROUP BY t1.yldGrowerID
UNION
SELECT t2.yldGrowerID, '0' AS SumYield1, SUM(t2.yldYield) AS SumYield2 FROM mbtd_yield_classic_round2 t2 GROUP BY t2.yldGrowerID) t3 GROUP BY t3.yldGrowerID
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Glad I could help!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now