SQL select rows with different values in two column

Question

I have database with 4-5 tables. I'm collecting data like this

N | Nom_  | Soni_  | Savdo  | Foyda  | Val(Currency)
1 | Apple |    5kg | 5000   |   500  | UZS
2 | Banana|    4kg | 7000   |   800  | UZS
3 | Peach |    2kg | 2      |   0.2  | USD

And I want to separate Currency column two column like below

N | Nom_  | Soni_  | Savdo  | Foyda  | Val1  |  Val2
1 | Apple |    5kg | 5000   |   500  | UZS   |
2 | Banana|    4kg | 7000   |   800  | UZS   |
3 | Peach |    2kg | 2      |   0.2  |       |   USD

Here is my SQL code.

select  s.nom_ , sum(o.soni_) as soni_ ,
   sum(o.soni_*(select narx_ from toper where id_=o.id_ )) as savdo_, 
   sum((o.soni_*o.narx_-o.soni_* (select narx_ from toper where id_=o.id_op ))) as foyda_,
   (select nom_ from tsinf where id_=o.id_v) as val_ 
      from toper o left join tsf f on o.id_sf=f.id_  
      left join tsinf s on o.id_s=s.id_  
where (f.date_>=date('2020-01-01')) and (f.date_<=date('2020-04-04')) 
and f.oper_=-1  group by id_s, id_v

Gordon Linoff · Accepted Answer · 2020-03-27 11:05:08Z

1

I would move the subquery to the FROM clause (using LEFT JOIN). Then the split is just two CASE expressions:

select s.nom_ , sum(o.soni_) as soni_,
       sum(o.soni_*(select narx_ from toper where id_=o.id_ )) as savdo_, 
       sum((o.soni_*o.narx_-o.soni_* (select narx_ from toper where id_=o.id_op ))) as foyda_,
        (case when sv.nom_1 = 'UZS' then sv.nom_1 end) as uzs,
        (case when sv.nom_1 = 'USD' then sv.nom_1 end) as usd
from toper o left join
     tsf f
     on o.id_sf = f.id_  left join
     tsinf s
     on o.id_s = s.id_ left join
     tsinf sv
     on sv.id_ = o.id_v
where f.date_ >= date('2020-01-01') and
      f.date_ <= date('2020-04-04') and 
      f.oper_ = -1 
group by id_s, id_v ;

I would also recommend removing the subqueries to toper, but that would be another question.

answered Mar 27, 2020 at 11:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hayrulla Melibayev Over a year ago

Thanks Gordon-Linoff it was very helpful

forpas · Accepted Answer · 2020-03-27 10:41:12Z

1

Put your query inside a CTE and then use a CASE expression to select the 2 columns:

with cte as (
  select s.nom_ , sum(o.soni_) as soni_ ,
    sum(o.soni_*(select narx_ from toper where id_=o.id_ )) as savdo_, 
    sum((o.soni_*o.narx_-o.soni_* (select narx_ from toper where id_=o.id_op ))) as foyda_,
    (select nom_ from tsinf where id_=o.id_v) as val_ 
  from toper o left join tsf f on o.id_sf=f.id_  
  left join tsinf s on o.id_s=s.id_  
  where (f.date_>=date('2020-01-01')) and (f.date_<=date('2020-04-04')) 
  and f.oper_=-1  
  group by id_s, id_v
)
select nom_, soni, savdo, foyda_, 
  case when val_ = 'UZS' then val_ end as Val1,
  case when val_ = 'USD' then val_ end as Val2 
from cte

But you should know that the condition:

where (f.date_>=date('2020-01-01')) and (f.date_<=date('2020-04-04'))

changes your left join to tsf to an inner join. If the results are what you expect then fine. If not move these conditions to the ON clause.

Also instead of all the select statements that you use in the select list, you could use joins if the relationships are 1:1.
Try this:

select  
  s.nom_, 
  sum(o.soni_) as soni_,
  sum(o.soni_* o2.narx_) as savdo_, 
  sum((o.soni_*o.narx_-o.soni_* o3.narx_) as foyda_,
  case when s2.nom_ = 'UZS' then s2.nom_ end as Val1,
  case when s2.nom_ = 'USD' then s2.nom_ end as Val2 
from toper o 
left join toper o2 on o2.id_=o.id_
left join toper o3 on o3.id_=o.id_op
left join tsf f on o.id_sf=f.id_  
left join tsinf s on o.id_s=s.id_ 
left join tsinf s2 on o.id_v=s2.id_  
where (f.date_>=date('2020-01-01')) and (f.date_<=date('2020-04-04')) and f.oper_=-1  
group by id_s, id_v

answered Mar 27, 2020 at 10:41

forpas

165k10 gold badges51 silver badges85 bronze badges

2 Comments

Hayrulla Melibayev Over a year ago

Thank you forpas for attention and spending time. Your version was also perfect.

forpas Over a year ago

@HayrullaMelibayev don't use subqueries in the select list. This is where you do joins between the tables and this is the reason why there are joins in sql. When you mix subqueries and joins you make your code difficult to maintain.

Collectives™ on Stack Overflow

SQL select rows with different values in two column

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related