HAVING query structured wrong
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
"Chuck" == Chuck Martin <clmartin@theombudsman.com> writes:
Chuck> I am trying to create a query that returns all transactions for
Chuck> each person who has a balance over a given amount. I thought
Chuck> HAVING was the answer, but if so, I'm mis-using it. This query
Chuck> returns only transactions exceeding the given amount rather than
Chuck> transactions for people whose balance is over the amount:
[snip]
Chuck> Since that returned the wrong set of records, I created another
Chuck> that returns the correct set of people with balances over the
Chuck> given amount. But I can't figure out how to use this to get all
Chuck> the transactions for people returned by this query:
Chuck> SELECT case_pkey
Chuck> FROM trans,ombcase,status
Chuck> WHERE case_fkey = case_pkey
Chuck> AND status_fkey = status_pkey
Chuck> AND statusopen = 1
Chuck> AND transistrust <> 1
Chuck> AND transcleared <> 1
Chuck> GROUP BY case_pkey
Chuck> HAVING sum(transamount) >= 50
Chuck> ORDER BY case_pkey
Chuck> So how do I get all transactions for each case_pkey?
You can join the result of any subquery as if it were a table, either
with or without using a CTE:
SELECT ...
FROM (select case_pkey from ... having ...) AS cases,
trans
WHERE trans.case_fkey = cases.case_pkey;
(incidentally, please qualify all the column references in your query
with a table name or alias, otherwise people reading your code have no
idea which column is supposed to be in which table)
or with a CTE,
WITH cases AS (select ... from ... having ...)
SELECT ...
FROM cases, trans
WHERE trans.case_fkey = cases.case_pkey;
There's also a third method with window functions instead of GROUP BY,
which is to do something like
SELECT ...
FROM (select ...,
sum(transamount) over (partition by case_pkey) as total_amt
from ...) s
WHERE total_amt > 50;
--
Andrew (irc:RhodiumToad)
On Mon, Feb 18, 2019 at 12:37 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:
"Chuck" == Chuck Martin <clmartin@theombudsman.com> writes:
Chuck> I am trying to create a query that returns all transactions for
Chuck> each person who has a balance over a given amount. I thought
Chuck> HAVING was the answer, but if so, I'm mis-using it. This query
Chuck> returns only transactions exceeding the given amount rather than
Chuck> transactions for people whose balance is over the amount:
[snip]
Chuck> Since that returned the wrong set of records, I created another
Chuck> that returns the correct set of people with balances over the
Chuck> given amount. But I can't figure out how to use this to get all
Chuck> the transactions for people returned by this query:Chuck> SELECT case_pkey
Chuck> FROM trans,ombcase,status
Chuck> WHERE case_fkey = case_pkey
Chuck> AND status_fkey = status_pkey
Chuck> AND statusopen = 1
Chuck> AND transistrust <> 1
Chuck> AND transcleared <> 1
Chuck> GROUP BY case_pkey
Chuck> HAVING sum(transamount) >= 50Chuck> ORDER BY case_pkey
Chuck> So how do I get all transactions for each case_pkey?
You can join the result of any subquery as if it were a table, either
with or without using a CTE:SELECT ...
FROM (select case_pkey from ... having ...) AS cases,
trans
WHERE trans.case_fkey = cases.case_pkey;
(incidentally, please qualify all the column references in your query
with a table name or alias, otherwise people reading your code have no
idea which column is supposed to be in which table)
Sorry. That was sloppy.
But working with this idea, I got the query working, so I appreciate the
pointer. For anyone wanting to see how I did so, here is the working query
(replacing the variable for the minimum balance to include with "50"):
-- Find transactions for client invoices using subquery to find client total
SELECT
contactnameaddress.fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,contactnameaddress.postalcode,
trans.transdate,linkedname.linkednameid,trans.transreference,
trans.transamount,ombcase.caseid,trans.transcheckno,contactnameaddress.lastorcompanyname,contactnameaddress.firstname
FROM trans,ombcase,client,contactnameaddress,linkedname,status ,
(SELECT case_pkey FROM ombcase,trans , status
WHERE trans.case_fkey = ombcase.case_pkey
AND ombcase.status_fkey = status_pkey
AND status.statusopen = 1
AND trans.transistrust <> 1
AND trans.transcleared <> 1
GROUP BY ombcase.case_pkey
HAVING sum(trans.transamount) >= 50) AS cases
WHERE trans.case_fkey = cases.case_pkey
AND trans.transistrust <> 1
AND ombcase.client_fkey = client.client_pkey
AND client.clientname_fkey = contactnameaddress.contactname_pkey
AND trans.linkedname_fkey = linkedname.contactname_pkey
AND ombcase.status_fkey = status.status_pkey
AND status.statusopen = 1
AND trans.transcleared <> 1
AND trans.Case_fkey = ombcase.case_pkey
GROUP BY ombcase.case_pkey,contactnameaddress.streetaddress,
contactnameaddress.towncityname, contactnameaddress.stateprovabbrev,
contactnameaddress.postalcode,
trans.transdate,trans.transreference,trans.transamount,
contactnameaddress.fullname,linkedname.linkednameid,
contactnameaddress.lastorcompanyname,
contactnameaddress.firstname,ombcase.caseid,
trans.transcheckno
I'll try to learn the other methods using your examples. Again, I
appreciate the help.
or with a CTE,
Show quoted text
WITH cases AS (select ... from ... having ...)
SELECT ...
FROM cases, trans
WHERE trans.case_fkey = cases.case_pkey;There's also a third method with window functions instead of GROUP BY,
which is to do something likeSELECT ...
FROM (select ...,
sum(transamount) over (partition by case_pkey) as total_amt
from ...) s
WHERE total_amt > 50;--
Andrew (irc:RhodiumToad)