Solved

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

Posted on 2014-10-01
8
270 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL inner join confusion 15 52
selecting date modified field from a table 2 44
Problem with Simple PHP/mySQL Query 3 66
RDBMS and No sql database 4 57
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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