[vikramsinh_shinde]

I have a query as follow,

SELECT payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created 
FROM tpayment_txn tt 
JOIN tbooking tb 
  ON tb.booking_ref_id = tt.booking_ref_id 
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)

which gives me result like ,

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  210              |   68 |     2011     |
|  201              |   34 |     2012     |
|  205              |   57 |     2011     |

(8 rows affected)

I have one more table named payment_txn_status_ref which contains description for each payment staus code as follow,

 
+---------------+-----------+
|payment_status |description|
+---------------+-----------+
|201            | Created   |
|202            | Progress  |
|202            | Collected |
|205            | Paid      |

So I want to modify above query so that it will replace payment_txn_status code with their respective description from payment_txn_status_ref table

The result should be look like ,

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  Created          |   68 |     2011     |
|  Progress         |   34 |     2012     |
|  Paid             |   57 |     2011     |

and so on....

[nK0de]

Again you can do that using JOINS. I've shown you how to join tables in your previous question. Now show some effort by trying to do it yourself first.

[vikramsinh_shinde]

nK0de, on 24 February 2012 - 03:08 AM, said:

Again you can do that using JOINS. I've shown you how to join tables in your previous question. Now show some effort by trying to do it yourself first.

i have tried this ---

SELECT ps.description AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
ON tb.booking_ref_id = tt.booking_ref_id
JOIN payment_txn_status_ref ps
ON payment_txn_status = payment_status
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)



but it's giving me error --

Column 'payment_txn_status_ref.description ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Am i missing something?

[nK0de]

change the GROUP BY clause to this and see

GROUP BY payment_txn_status, date_created

[vikramsinh_shinde]

nK0de, on 24 February 2012 - 03:41 AM, said:

change the GROUP BY clause to this and see

GROUP BY payment_txn_status, date_created

still it's giving same error --

Column 'payment_txn_status_ref.description ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[nK0de]

check your second JOIN statement.

JOIN payment_txn_status_ref ps ON payment_txn_status = payment_status

And give this article a read. It'll help you out.

This post has been edited by nK0de: 24 February 2012 - 03:59 AM

[vikramsinh_shinde]

it get solved ..... many thanks..

SELECT ps.payment_status_desc AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
  ON tb.booking_ref_id = tt.booking_ref_id
JOIN tpayment_txn_status_ref ps 
  ON tt.payment_txn_status = ps.payment_status
GROUP BY ps.payment_status_desc, YEAR(tb.date_created)