?
Solved

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

Posted on 2014-10-01
8
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40354931
Can you please provide some sample data?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40355484
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
ID: 40356339
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 total points
ID: 40356355
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40356444
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 35

Expert Comment

by:Dan Craciun
ID: 40356480
@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
ID: 40356652
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 35

Expert Comment

by:Dan Craciun
ID: 40356683
Glad I could help!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 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