I have a table currency_lkp and a table associate_data The associate_data has these columns - currency, invoice_total and status (Unpaid or Paid or WaitingI) I can succesfully link the tables with the top half of this query (see MySQL fiddle) and provide a total for each currency.
I can also provide a correct total for each status converted into USD (see the bottom half of the query Which produces one line
When I use UNION ALL between the two halves it produces a result but very strange figures in the middle three columns but the correct result in the 4th column and the final row of that column.
I think the query is OK but could be neater if it was possible to use variables with the sub-queries. Any ideas on what is going wrong or how to improve the elegance will be greatly appreciated - thank you
To create a sample db: (MySQL query below this)
CREATE TABLE IF NOT EXISTS `currency_lkp` (
`currency_id` int(11) NOT NULL AUTO_INCREMENT,
`currency` varchar(10) NOT NULL,
`currency_label` varchar(100) NOT NULL,
`currency_description` varchar(100) NOT NULL,
`exchange_rate_USD` float NOT NULL,
PRIMARY KEY (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `currency_lkp` (`currency_id`, `currency`,
`currency_label`, `currency_description`, `exchange_rate_USD`)
VALUES
(1, 'GBP', '£', 'Great British Pound (Sterling)', 0.75),
(2, 'Euro', '€', 'Euro currency for the Euro Zone', 0.85),
(3, 'USD', '$', 'United States Dollars', 1),
(4, 'CLP', '$', 'Chilean Pesos', 639.85),
(5, 'INR', '₹', 'Indian Rupees', 64.8);
CREATE TABLE IF NOT EXISTS `associate_data` (`associate_data_id`
int(11) NOT NULL AUTO_INCREMENT,
`currency` varchar(10) NOT NULL,
`status` varchar(20) NOT NULL,
`invoice_total` float,
PRIMARY KEY (`associate_data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `associate_data` (`associate_data_id`, `currency`,
`status`, `invoice_total`)
VALUES
(1, 'GBP', 'Paid', 200),
(2, 'GBP', 'Unpaid', 300),
(3, 'Euro', 'Paid', 1000),
(4, 'Euro', 'Unpaid', 2000),
(5, 'USD', 'Paid', 1200),
(6, 'USD', 'Unpaid', 1400),
(7, 'GBP', 'WaitingInvoice', 400),
(8, 'Euro', 'WaitingInvoice', 1300),
(9, 'USD', 'WaitingInvoice', 2300),
(10, 'CLP', 'Paid', 100200),
(11, 'CLP', 'Unpaid', 110400),
(12, 'CLP', 'WaitingInvoice', 11200);
MySQL Query in full:
`
(
SELECT cl.currency,
(SELECT SUM(invoice_total) FROM associate_data ad2
WHERE ad2.currency = cl.currency AND ad2.status = 'Paid') AS Paid,
(SELECT SUM(invoice_total) FROM associate_data ad3
WHERE ad3.currency = cl.currency AND ad3.status = 'Unpaid') AS Unpaid,
(SELECT SUM(invoice_total) FROM associate_data ad4
WHERE ad4.currency = cl.currency AND ad4.status = 'WaitingInvoice') AS WaitingInvoice,
(SELECT IFNULL(Unpaid,0) + IFNULL(WaitingInvoice, 0)) AS TotalToPay
FROM currency_lkp cl
)
UNION ALL
(
SELECT 'Total in USD',
FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad5
WHERE ad5.currency = clu.currency AND (ad5.status = 'Paid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDPaid,
FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDUnpaid,
FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDWaitingInvoice,
FORMAT(
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice' OR ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
,0) AS TotalUSDToPay
FROM currency_lkp clu
)`
You can radically simplify your query (and improve efficiency) by using "conditional aggregates" (a case expression INSIDE the aggregate function) like this:
select
cu.currency
, sum(case when ad.status = 'Paid' then invoice_total end) as Paid
, sum(case when ad.status = 'Unpaid' then invoice_total end) as Unpaid
, sum(case when ad.status = 'WaitingInvoice' then invoice_total end) as WaitingInvoice_USD
, FORMAT(sum(case when ad.status = 'Paid' then invoice_total/cu.exchange_rate_USD end),0) as Paid_USD
, FORMAT(sum(case when ad.status = 'Unpaid' then invoice_total/cu.exchange_rate_USD end),0) as Unpaid_USD
, FORMAT(sum(case when ad.status = 'WaitingInvoice' then invoice_total/cu.exchange_rate_USD end),0) as WaitingInvoice_USD
FROM associate_data ad
inner join currency_lkp cu ON ad.currency = cu.currency
group by
cu.currency
union all
select
'Total'
, sum(case when ad.status = 'Paid' then invoice_total end) as Paid
, sum(case when ad.status = 'Unpaid' then invoice_total end) as Unpaid
, sum(case when ad.status = 'WaitingInvoice' then invoice_total end) as WaitingInvoice_USD
, FORMAT(sum(case when ad.status = 'Paid' then invoice_total/cu.exchange_rate_USD end),0) as Paid_USD
, FORMAT(sum(case when ad.status = 'Unpaid' then invoice_total/cu.exchange_rate_USD end),0) as Unpaid_USD
, FORMAT(sum(case when ad.status = 'WaitingInvoice' then invoice_total/cu.exchange_rate_USD end),0) as WaitingInvoice_USD
FROM associate_data ad
inner join currency_lkp cu ON ad.currency = cu.currency
;
That query produces the following result:
| currency | Paid | Unpaid | WaitingInvoice_USD | Paid_USD | Unpaid_USD | WaitingInvoice_USD |
|----------|--------|--------|--------------------|----------|------------|--------------------|
| CLP | 100200 | 110400 | 11200 | 157 | 173 | 18 |
| Euro | 1000 | 2000 | 1300 | 1,176 | 2,353 | 1,529 |
| GBP | 200 | 300 | 400 | 267 | 400 | 533 |
| USD | 1200 | 1400 | 2300 | 1,200 | 1,400 | 2,300 |
| Total | 102600 | 114100 | 15200 | 2,800 | 4,325 | 4,380 |
see this demo: http://sqlfiddle.com/#!9/b4b5bb/36
The reason for the gobbledygook in some columns of your former query was attempting to union numbers and formatted (text) data in the same columns. Remove the format( ,0)
see: http://sqlfiddle.com/#!9/b4b5bb/37
(
SELECT cl.currency,
(SELECT SUM(invoice_total) FROM associate_data ad2
WHERE ad2.currency = cl.currency AND ad2.status = 'Paid') AS Paid,
(SELECT SUM(invoice_total) FROM associate_data ad3
WHERE ad3.currency = cl.currency AND ad3.status = 'Unpaid') AS Unpaid,
(SELECT SUM(invoice_total) FROM associate_data ad4
WHERE ad4.currency = cl.currency AND ad4.status = 'WaitingInvoice') AS WaitingInvoice,
(SELECT IFNULL(Unpaid,0) + IFNULL(WaitingInvoice, 0)) AS TotalToPay
FROM currency_lkp cl
)
UNION ALL
(
SELECT 'Total in USD',
SUM(IFNULL(
(
SELECT SUM(invoice_total) FROM associate_data ad5
WHERE ad5.currency = clu.currency AND (ad5.status = 'Paid')
),0.1)/clu.exchange_rate_USD)
AS TotalUSDPaid,
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
AS TotalUSDUnpaid,
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice'))
,0.1)/clu.exchange_rate_USD)
AS TotalUSDWaitingInvoice,
SUM(IFNULL(
(SELECT SUM(invoice_total) FROM associate_data ad6
WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice' OR ad6.status = 'Unpaid'))
,0.1)/clu.exchange_rate_USD)
AS TotalUSDToPay
FROM currency_lkp clu
)
and the result of that query is:
| currency | Paid | Unpaid | WaitingInvoice | TotalToPay |
|--------------|--------------------|------------------|-------------------|-------------------|
| GBP | 200 | 300 | 400 | 700 |
| Euro | 1000 | 2000 | 1300 | 3300 |
| USD | 1200 | 1400 | 2300 | 3700 |
| CLP | 100200 | 110400 | 11200 | 121600 |
| INR | (null) | (null) | (null) | 0 |
| Total in USD | 2799.7379740260294 | 4325.48309943104 | 4380.250701542091 | 8705.732257763328 |
NB: I have not attempted to correct any query arithmetic, but clearly one of them is wrong