For example, there are two tables, and in one - prices with articles, and another table - checks, some articles in checks, and quantity of article

TABLE checks

TABLE articles

and i need to count the sum of sales of 1check,where i need to take the second minimum of price if the articles repeat. for 1toy in 1check the price have to be 2.5.- for sum (quantity*price) i try to write a code - but i finally confused. help please

SELECT
    a.check,
    Sump
FROM
    (
        SELECT
            price2,
            Case
                WHEN COUNT( a.art ) > 1 THEN
                    SUM( a.quantity * a.price ) 
                ELSE
                    SUM( a.quantity * price2 )
            END AS sump,
            a.art,
            a.check
        FROM
            checks AS a
            INNER JOIN
            (
                SELECT
                    art,
                    price,
                    LEAD( price, 1 ) OVER (
                        PARTITION BY art
                        ORDER BY price ASC
                    ) AS price2
            FROM
                prices
            ) AS b on a.art = b.art
        WHERE
            a.quantity > 0
        GROUP BY
            a.checks,
            a.art,
            price2
    )
WHERE
    a.checks = '1check' 

CodePudding user response：

You may use the ROW_NUMBER() and COUNT window functions as the following:

SELECT T.checks, T.art, SUM(T.quantity * D.price) sump
  FROM
  checks T JOIN
  ( 
    SELECT art, price,
       COUNT(*) OVER (PARTITION BY art) cn,
       ROW_NUMBER() OVER (PARTITION BY art ORDER BY price) rn
    FROM articles
  ) D
ON T.art = D.art
WHERE (D.cn = 1 OR D.rn = 2) AND T.checks = '1check'
GROUP BY T.checks, T.art
ORDER BY T.checks, T.art

The WHERE (D.cn = 1 OR D.rn = 2) ensures that the returned price is the second minimum (rn=2) or it's only the existed price (cn=1).

The output according to your provided data:

See a demo on Oracle 11g.

CodePudding user response：

We use dense_rank() to find the second lowest price in the case count(*) > 1. Then we merge the tables, group by art and total the sales.

with a  as (
select  art
       ,price
from   
        (
        select   a.*
                ,dense_rank() over(partition by art order by price) as dns_rnk
                ,count(*) over(partition by art)                    as cnt
        from    articles a
        ) a 
where   cnt > 1 and dns_rnk = 2
or      cnt = 1 
          )

  
select   art
        ,sum(quantity)*price as total
from     a left join checks c using(art)
group by art, price
order by art

Fiddle