Tell me, please, the options for the script to select.
There are 2 tables: customers and products (purchased by the customer).
We must choose all customers who have bought products worth more than 5 thousand rubles. Clients bought products in rubles and dollars (dollar exchange rate = 65 rubles). There is a currency code field in the products table (ruble code 1 and dollar code 2).

  • You though show the scheme of tables. - Suvitruf ♦
  • "choose all customers who bought products for the sum" - probably it is necessary for some period of time? - Stack
  • Without a scheme - difficult - BuilderC
  • Not for the period, but all customers who have made a purchase in the amount of more than 5 thousand - Sergey4590

2 answers 2

The layout of the tables, of course, would be very helpful ... something like this:

SELECT c.*, SUM(decode(p.currency,'RUR',p.price, p.price*65)) FROM clients c JOIN products p ON p.client_id=c.id GROUP BY c.id HAVING SUM(decode(p.currency,'RUR', p.price, p.price*65))>5000

RUR - currency code ruble.

Maybe this will help ... you can translate to plsql ...

I know that this is not the answer ... but the SQL is similar, so at least I will push :)

  • one
    "SUM (IF (p.currency = 1, p.price, p.price * 65))" - instead of IF there will be CASE - like this: SUM (case when p.currency = 1 then p.price else p.price * 65 end) - Stack
  • 2
    Still in the group by you must specify all the fields that are selected without aggregate functions. So unfortunately c. * It will be impossible to write. It is necessary both in select and in group to set explicit lists of the necessary fields. And I will correct your answer a little, that would fit the dialect - Mike
  • I use this option, the script works for a long time. Xs how to improve performance. - Sergey4590

Probably this will be faster earlier offered here

select c.*, pp.client_sum from clients c, ( SELECT p.client_id, SUM(decode(p.currency,'RUR',p.price, p.price*65)) client_sum FROM products p GROUP BY p.client_id HAVING SUM(decode(p.currency,'RUR', p.price, p.price*65))>5000 ) pp where pp.client_id=c.id