CODEWITHSUNDEEP
sqlselect
Nabil Sham
Nabil Sham

Reputation: 2355

SQL join from the same table

How do I get grand total for orders made by credit card only from such a table :

order_id | meta_name | meta_value
___________________________________
1        | type      | credit
1        | total     | 1030.00
...
2        | type      | check
2        | total     | 930.00
..

3        | type      | credit
3        | total     | 330.00

what is the best way to describe such select operation if you are to search the Internet for a solution to this problem. suppose I am MySQL.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this with either join or group by. Here is the join method:

select ttype.order_id, cast(ttotal.meta_value as money)
from table ttype join
     table ttotal
     on ttype.order_id = ttotal.order_id and
        ttype.meta_name = 'type' and
        ttype.meta_value = 'credit' and
        ttotal.meta_name = 'total';

If yo could have more than one total for an order, then you would still want to aggregate:

select ttype.order_id, sum(cast(ttotal.meta_value as money))
from table ttype join
     table ttotal
     on ttype.order_id = ttotal.order_id and
        ttype.meta_name = 'type' and
        ttype.meta_value = 'credit' and
        ttotal.meta_name = 'total'
group by ttype.order_id

Upvotes: 3

Related Questions