Solved

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

Posted on 2014-10-01
8
269 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
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
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 34

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 34

Expert Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Please help me with my database modeling 7 38
Database Design Concept 3 50
understanding output of mysql version 2 50
Have issues with Query MySQL 9 54
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

912 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

21 Experts available now in Live!

Get 1:1 Help Now