From: | Chuck Martin <clmartin(at)theombudsman(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | HAVING query structured wrong |
Date: | 2019-02-18 17:20:02 |
Message-ID: | CAFw6=U1HjkB-V5SWmi60m6LvDJpfSg26rLD37dPAdKfGQKOfNw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to create a query that returns all transactions for each person
who has a balance over a given amount. I thought HAVING was the answer, but
if so, I'm mis-using it. This query returns only transactions exceeding the
given amount rather than transactions for people whose balance is over the
amount:
SELECT
fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,contactnameaddress.postalcode,
transdate,linkednameid,transreference,
transamount,caseid,transcheckno,lastorcompanyname,firstname
FROM trans,ombcase,client,contactnameaddress,linkedname,status
WHERE transistrust <> 1
AND client_fkey = client_pkey
AND case_fkey = case_pkey
AND clientname_fkey = contactnameaddress.contactname_pkey
AND linkedname_fkey = linkedname.contactname_pkey
AND status_fkey = status_pkey
AND lower(statusid) NOT LIKE ('closed%')
AND transcleared <> 1
GROUP BY case_pkey,contactnameaddress.streetaddress,
contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,
contactnameaddress.postalcode,
transdate,transreference,transamount,
fullname,linkednameid,
contactnameaddress.lastorcompanyname,
contactnameaddress.firstname,caseid,
transcheckno
HAVING sum(transamount)>= 50
Since that returned the wrong set of records, I created another that
returns the correct set of people with balances over the given amount. But
I can't figure out how to use this to get all the transactions for people
returned by this query:
SELECT case_pkey
FROM trans,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND statusopen = 1
AND transistrust <> 1
AND transcleared <> 1
GROUP BY case_pkey
HAVING sum(transamount) >= 50
ORDER BY case_pkey
So how do I get all transactions for each case_pkey? I've read the
documentation on WITH clauses (CTEs), but that just left my head spinning.
Chuck Martin
Avondale Software
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-02-18 17:37:10 | Re: HAVING query structured wrong |
Previous Message | Jitendra Loyal | 2019-02-18 17:11:11 | Re: BEFORE ... Statement-level trigger |