CODEWITHSUNDEEP
sql
Karim
Karim

Reputation: 1

Not Exists query

I'm trying to find the clients, those who didn't order in the last 2 years and they ordered this year more than 500.. I wrote this query and I used the "NOT EXISTS" condition, but it is still showing me the wrong results. Some suggestions would be appreciated.

My code:

SELECT
         "Sales"."Kundennummer" as 'Neuer Kunde',
         year("Sales"."Datum"),
         sum("Sales"."Umsatz mit Steuer") as "Umsatz"
FROM  "Sales" 
WHERE year("Sales"."Datum")  = '2017'
 AND NOT EXISTS
    (
    SELECT "Sales"."Kundennummer"
    FROM  "Sales" 
    WHERE    year("Sales"."Datum")  = '2015'
     AND    year("Sales"."Datum")  = '2016'
    )
GROUP BY
 "Sales"."Kundennummer",
      "Sales"."Datum" 
HAVING sum("Sales"."Umsatz mit Steuer")  > 500

Upvotes: 0

Views: 70

Answers (3)

Slim
Slim

Reputation: 1

I propose you here below 3 different ways to do it:

  1. Joining 2 tables

    select this_year_sales.kundenummer, this_year_sales.tot_umsatz
from (select sum(umsatz) tot_umsatz, kundenummer from sales where extract(year from (datum)) = extract(year from sysdate) group by kundenummer) this_year_sales
, (select kundenummer, max(datum) max_datum from sales where datum < trunc(sysdate, 'year') group by kundenummer) previous_sales
where this_year_sales.kundenummer = previous_sales.kundenummer
and extract(year from previous_sales.max_datum) < (extract(year from sysdate)-2) 
and this_year_sales.tot_umsatz > 500;

  2. Using NOT INT

    select kundenummer, sum(umsatz)
from sales s
where extract(year from datum) = extract(year from sysdate)
and kundenummer not in (select kundenummer from sales where extract(year from datum) > (extract(year from sysdate) - 2) and extract(year from datum) < (extract(year from sysdate)-1))
group by kundenummer
having sum(umsatz) > 500;

  3. Using NOT EXISTS

    select kundenummer, sum(umsatz)
from sales s
where extract(year from datum) = extract(year from sysdate)
and not exists(
select s1.kundenummer, s1.datum from sales s1 where extract (year from s1.datum) >= (extract(year from sysdate)-2) and extract(year from s1.datum) < extract (year from sysdate) and s1.kundenummer = s.kundenummer
)
group by kundenummer
having sum(umsatz) > 500;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Your EXISTS query is not correlated to the main query, i.e. it doesn't look for data for the Kundennummer in question, but whether there are any records in 2015 and 2016.

(You also have the condition for the years wrong by using AND where it must be OR and you should not use quotes on numbers like 2015', and you should not use single quotes on names like 'Neuer Kunde'.)

It should be

AND NOT EXISTS
(
  SELECT *
  FROM Sales other_years
  WHERE other_years.Kundennummer = Sales.Kundennummer
    AND year(other_years.Datum) in (2015, 2016)
)

or uncorrelated with NOT IN

AND Kundennummer NOT IN
(
  SELECT Kundennummer
  FROM  Sales
  WHERE year(Datum) in (2015, 2016)
)

Be aware though, that when using NOT IN the subquery must return no nulls. E.g. where 3 not in (1, 2, null) does not result in true, as one might expect, because the DBMS argues that the unknown value (null) might very well be a 3 :-)

Upvotes: 0

Tobb
Tobb

Reputation: 12180

The query in the NOT EXISTS clause will probably yield 0 rows, since a row can't have Datum both 2015 and 2016. So it should probably be OR instead of AND.

Also, if you fix this, there is no link between the subquery and the superquery, which means that it will return rows for any customer (given that there exists a row with Datum either 2015 or 2016 in your table which I guess it does).

So, something like:

SELECT
         "Sales"."Kundennummer" as 'Neuer Kunde',
         year("Sales"."Datum"),
         sum("Sales"."Umsatz mit Steuer") as "Umsatz"
FROM  "Sales" sales
WHERE year("Sales"."Datum")  = '2017'
 AND NOT EXISTS
    (
    SELECT "Sales"."Kundennummer"
    FROM  "Sales" salesI
    WHERE salesI."Kundennummer" = sales."Kundennummer"
      AND (year("Sales"."Datum")  = '2015'
           OR year("Sales"."Datum")  = '2016')
    )
GROUP BY
 "Sales"."Kundennummer",
      "Sales"."Datum" 
HAVING sum("Sales"."Umsatz mit Steuer")  > 500

Upvotes: 1

Related Questions