Click here to Skip to main content
15,042,397 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two queries

first -

SQL
select order_type, sum(order_value)as "Total"
from tpayment_order
group by order_type


which gives me result as -

SQL
order_type    Total
  C           345439
  P           5435
  T           787



Second -

SQL
select description from order_status_ref


which gives me result as
SQL
description
Collection
Payout
Travel Payout


I want to write a query which will give result as

SQL
Order_type    description    Total 
C              Collection    4565
P              Payout        4423
T            Travel Payout   456546



The
SQL
order_status_ref
table stores the description for each order type
Posted

Hi,

Try joining
SQL
SELECT A.order_type, sum(A.order_value) AS "Total", B.Description
FROM tpayment_order A, order_status_ref B
WHERE A.order_type=B.order_type
GROUP BY order_type, order_status_ref


But a vital assumption is that the order_status_ref is table with the following mapping:

SQL
order_type | Description


Without this mapping, the joining would be meaningless.


*Mark as answer if this solves your query.
   
Comments
vikram_shinde 23-Feb-12 10:39am
   
yah.. that's the mapping ....
Rajesh Anuhya 23-Feb-12 10:44am
   
You mean order_status_ref have only one column i.e description??
--RA
Jyothikarthik_N 23-Feb-12 10:41am
   
Then this query should solve. Do try it!
vikram_shinde 23-Feb-12 10:56am
   
order_status_ref has two column - order_type and description
Use INNER JOIN[^]
   
It get solved ---- many thanks.... I have used following query


SQL
SELECT T.order_type, R.[description], SUM(order_value) AS [Total]
FROM tpayment_order T
INNER JOIN tpayment_order_type_ref R ON T.order_type = R.order_type
GROUP BY T.order_type, R.[description]
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900