SQL - I have a 'People' table and an 'Account' table. How do I list all of the people in rows, and have a balance column for each account type?

sql having multiple conditions
sql having max
sql having vs where
having clause in sql without group by
having mysql
group by sql
sql exists
sql query for finding records where count > 1

I was having a hard time figuring out how to title this question, so my apologies in advance.

Here is my my situation:

I have one table that has just people in it. I have another table that has all the accounts, with a personID, accountType, and balance column. A person can have multiple accounts, there are different account types, and not everyone has all of the different account types.

How can I write a query where I list one person per row, and have a column for the balance of each "account type"?

Ideally, my query would look something like this:

PersonID | Account Type 1 Bal | Account Type 2 Bal | Account Type 3 Bal |
-------------------------------------------------------------------------
    1    |         $100       |         null       |         null       |
    2    |         null       |         $12        |         $1300      |
    3    |         null       |         null       |         $5         |
    4    |         $150       |         null       |         null       |
    5    |         $65        |         $300       |         $45        |

I would assume I would use some sort of case statement, but I haven't been able to figure it out yet. Also, if they have multiple of the same account type, i assume I would just use sum() correct?

Thanks.

Something like this should work for postgres

select p.person_id,
  (select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type1') type1_balance,
  (select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type2') type2_balance,
  (select sum(a.balance) from account a where a.person_id = p.person_id and a.account_type = 'Type3') type3_balance
from person p

sqlfiddle example

SQL HAVING with GROUP BY, The SQL HAVING syntax. The general syntax is: SELECT column-names; FROM table-name  Online. On demand. Join over 100,000 students who have taken the course.

Check if this helps -

http://sqlfiddle.com/#!4/30ebb/10/0

CREATE TABLE person
    ("person_id" int, "name" varchar2(9))
;

INSERT ALL 
    INTO person ("person_id", "name")
         VALUES (1, '''Abcd''')
    INTO person ("person_id", "name")
         VALUES (2, '''xyz''')
    INTO person ("person_id", "name")
         VALUES (3, '''jjjjj''')
    INTO person ("person_id", "name")
         VALUES (4, '''sfds''')
    INTO person ("person_id", "name")
         VALUES (5, '''temp''')
SELECT * FROM dual
;

CREATE TABLE accounts
    ("personID" int, "accountType" int, "balance" int)
;

INSERT ALL 
    INTO accounts ("personID", "accountType", "balance")
         VALUES (1, 1, 100)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (1, 2, 150)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (2, 1, 20)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (3, 1, 40)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (3, 2, 440)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (4, 1, 600)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (5, 1, 43)
    INTO accounts ("personID", "accountType", "balance")
         VALUES (5, 2, 50)
SELECT * FROM dual
;

Query -

select * from (
select p."person_id", a."accountType", a."balance"
from person p, accounts a
where p."person_id" = a."personID"
)
pivot (sum("balance") for "accountType" in (1 as acc_type1_bal,2 as acc_type2_bal));


person_id   ACC_TYPE1_BAL   ACC_TYPE2_BAL
1   100 150
2   20  (null)
4   600 (null)
5   43  50
3   40  440

Having (SQL), A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added​  The SQL HAVING Clause. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. HAVING Syntax

select personid
      ,sum(ac1bal) accounttype1bal
      ,sum(ac2bal) accounttype2bal
      ,sum(ac3bal) accounttype3bal
from (
select persontable.personid
        ,case when (accounttype=1) then bal end ac1bal
        ,case when (accounttype=2) then bal end ac2bal
        ,case when (accounttype=3) then bal end ac3bal
from persontable
left join accounttable on persontable.personid = accounttable.personid
    )
group by personid

HAVING (Transact-SQL), Examples: Azure Synapse Analytics (SQL DW) and Parallel Data Warehouse. The following example uses a HAVING clause to retrieve the  SQL script to report version, edition, and servicing level: The Microsoft OneScript team has released a script to run in SQL Server Management Studio that can be used to determine the following: For more information about this script, see Determining the version and edition of SQL Server and its components in TechNet Gallery.

MySQL GROUP BY and HAVING Clause with Examples, language because the WHERE keyword could not be used with aggregate functions. For example, result of below query is: Query: select name from sys.parameters where object_id = @@PROCID; Result: name @BKP_TBL @Filter I want to store the actual value of @BKP_TBL and @Filter

Difference between WHERE vs HAVING clause in SQL, clause is used to restrict the results returned by the GROUP BY clause. The fun part is that SQL is not a full fledged programming language. Its very simple and close to English. So once you have the basics you are good to use it. You will definitely use an Interface (like SSMS or SQL developer) to communicate with database.

Using having without group by, This tutorial shows you how to use the SQL Server HAVING clause to filter the groups based on specified conditions. SQL, SQL Server, SQL Tips and Tricks 3 Comments Here is the first thing I do when I get access to any new server – I check what are different permissions I have with respect to the database I am connected with.

Comments
  • Which database are you using?
  • Intersystems Cache. My apologies, I'll update the question