CODEWITHSUNDEEP
mysql
fawad
fawad

Reputation: 1353

union of two queries to get the result in mysql

I'm confused about this:

SELECT request_id, SUM( cc_amount ) AS amt,
       DATE_FORMAT( transaction_datetime,  '%b %y' ) AS tdate
  FROM ee_request_cc
 GROUP BY DATE_FORMAT( transaction_datetime,  '%b %y' ) 

UNION 

SELECT request_id, SUM( request_amount ) AS amt,
       DATE_FORMAT( transaction_date,  '%b %y' ) AS tdate
  FROM ee_request_paypal
 GROUP BY DATE_FORMAT( transaction_date,  '%b %y' )

I'm getting:

id    amt    tdate
20  86.00   Mar 12
80   5.00   Apr 12
23  55.00   Mar 12

Whereas I want to add all amounts for March and April like:

id     amt   tdate
20  141.00  Mar 12
80    5.00  Apr 12

Please suggest me the change in my query.

Upvotes: 1

Views: 69

Answers (3)

GavinCattell
GavinCattell

Reputation: 3963

You need to get all the results, and then perform the aggregation:

SELECT table_both.request_id, table_both.tdate, SUM(table_both.amount) AS amt
FROM (SELECT request_id, cc_amount AS amt, DATE_FORMAT(transaction_datetime,'%b %y') AS tdate
    FROM table1 

    UNION ALL --Don't remove the duplicates

    SELECT request_id, request_amount, DATE_FORMAT(transaction_date,'%b %y')
    FROM table2) table_both
GROUP BY table_both.request_id, table_both.tdate --You don't need to add the format function in the group by

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

select sum(amt), DATE_FORMAT(tdate, '%b %y') from (
    SELECT amount1 AS amt, transaction_dt AS tdate
    FROM table1
    UNION ALL
    SELECT request_amount, tr_date
    FROM table2
) s
GROUP BY YEAR(tdate), MONTH(tdate)

Note you shouldn't take an ID from a grouped result... which one would you take?

Also grouping by numbers must be faster than transforming a date to a string and then grouping by string.

Upvotes: 1

grifos
grifos

Reputation: 3381

You use an englobing SELECT with sum on amt and GROUP BY tdate

SELECT id, SUM(amt) as samt, tdate
FROM (YOUR_ABOBE_QUERY) AS thequery
GROUP BY thequery.tdate

Upvotes: 0

Related Questions