SQL / SQlite nested select in having clause

Question

I was playing a bit with sqlite and encountered the following problem: it looks like it is not possible to nest a subquery in a having clause

When I try to call a query with:

... having count(select * from produkt) > 1

I get the error:

OperationalError: near "select": syntax error

While executing

 ... having count(1) > 1

everything is fine

Would you have any workarounds for that?

edit2:

I want to write this:

select distinct standort.name, standort.ort
from produktion
    join standort on id_standort = standort.id
    join produkt on id_produkt = produkt.id
where produkt.gewicht < 1

EXCEPT

select distinct standort.name, standort.ort
from produktion
    join standort on id_standort = standort.id
    join produkt on id_produkt = produkt.id
where produkt.kategorie = "Spiel"

In a more elegant way, using "having"

Cheers and thanks a lot!

Wojtek

Include the query in the question, as text. You have enough reputation to know that questions should be complete and not refer to off-site pages (except for additional helpful information). — Gordon Linoff
– Gordon Linoff, Commented May 30, 2017 at 17:40
@GordonLinoff ok done, i just thought it would be easier with an interactive jupyter notebook — Wojciech Kulma
– Wojciech Kulma, Commented May 30, 2017 at 17:49
I guess I don't understand your intention from your select statement. What I read is that you are selecting from produktion, grouping by standort, but don't want to get back any relults if produkt table is empty. But what I think you might be say is "select from produktion, group by standort but exclude any group where there is no matching produkt" Yes? — BenCamps
– BenCamps, Commented May 30, 2017 at 18:15
@bit2know I simply want to get all "Standort" satisfying 2 conditions: 1. produkt.kategorie is not "Spiel" 2. There is at least one produkt.gewicht > 1 — Wojciech Kulma
– Wojciech Kulma, Commented May 30, 2017 at 18:27

BenCamps · Accepted Answer · 2017-05-30 18:42:42Z

2

I think this fits your intention better than your current select

SELECT ... 
FROM Standort
     JOIN produktion ...
     JOIN produkt ...
WHERE product.kategorie != "Spiel" AND produkt.gewicht > 1

answered May 30, 2017 at 18:42

BenCamps

1,85417 silver badges26 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

This is not the same thing. This assumes the values are in the same row. So, I understand the answer, but not the upvotes.

BenCamps Over a year ago

What do you mean "values are in the same row"? it's a inner join

Gordon Linoff Over a year ago

Each standort.name, standort.ort combination could appear on multiple rows. The kategorie condition and gewicht conditions could be on different rows (produced by the inner join).

Gordon Linoff · Accepted Answer · 2017-05-31 00:14:03Z

1

I'm not sure in what context you would do this, but this is syntactically correct:

having (select count(*) from produkt) > 1

EDIT:

For your actual question, this query is simpler:

select s.name, s.ort
from produktion pn join
     standort s
     on pn.id_standort = s.id join
     produkt p
     on pn.id_produkt = p.id
group by s.name, s.ort
having sum(case when p.gewicht < 1 then 1 else 0 end) > 0 and
       sum(case when p.kategorie = 'Spiel' then 1 else 0 end) = 0;

This returns all s.name/s.ort combinations that have a "gewicht" less than 1 and no "kategorie" called 'Spiel'.

edited May 31, 2017 at 0:14

answered May 30, 2017 at 17:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL / SQlite nested select in having clause

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related