CODEWITHSUNDEEP
mysql
Ka Oma
Ka Oma

Reputation: 23

how to combine results of two different queries from one table mysql

I am using MySQL in phpMyAdmin, I have a table from which I am trying to get the following result as illustrated below:

I have a table Rate structured as follows;

+---------+-----------+-------+--------+---------+----------+
| EntryID | RegDate   |  Code | Buying | Selling | Averages |
+---------+-----------+-------+--------+---------+----------+
| 1       |2013-11-08 |  USD  | NULL   | NULL    | 0.814    |
+---------+-----------+-------+--------+---------+----------+   
| 2       |2013-11-08 |  GBP  | NULL   | NULL    | 0.114    |
+---------+-----------+-------+--------+---------+----------+

With the primary key as EntryID I am trying to get the output as shown below;

+-----------+-------+-------+
| RegDate   |  USD  |  GBP  |
+-----------+-------+-------+
|2013-11-08 | 0.814 | 0.114 |
+---------+---------+-------+

Upvotes: 1

Views: 302

Answers (4)

Edper
Edper

Reputation: 9322

Try:

SELECT EntryID, regDate,
SUM(CASE Code WHEN 'USD' THEN Averages ELSE 0 END) as USD,
SUM(CASE Code WHEN 'GBP' THEN Averages ELSE 0 END) as GBP
FROM TableA
GROUP BY regDate

See Demo

Upvotes: 0

valex
valex

Reputation: 24144

SELECT RegDate,
       SUM(CASE WHEN Code='USD' THEN Averages ELSE 0 END) as USD,
       SUM(CASE WHEN Code='GBP' THEN Averages ELSE 0 END) as GBP
FROM T
GROUP BY RegDate

SQLFiddle demo

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can simply JOIN the table with itself, use the table once for each currency you need;

SELECT usd.RegDate, usd.averages USD, gbp.averages GBP
FROM Table1 usd
JOIN Table1 gbp
  ON usd.regdate = gbp.regdate
 AND usd.code = 'USD'
 AND gbp.code = 'GBP';

An SQLfiddle to test with.

Note that this query assumes that each currency exists exactly once per date to give a good result, if that's not the case, you should add that to your sample data.

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT USD.RegDate, USD.Averages as USD,
GBP.Averages as GBP From
(SELECT RegDate, Averages
From Table1 where Code = 'USD') USD
Inner join
(SELECT RegDate, Averages
From Table1 where Code = 'GBP') GBP
ON USD.RegDate = GBP.RegDate

Sample Fiddle

Upvotes: 0

Related Questions