Trying to learn SQL and need help writing a super easy query

Question

This is probably the easiest query you'll ever have to write, but I'm not quite sure how to wrap my head around this yet. I know some basics with SQL, but haven't even modified a query in years. I'm hoping for help so I can digest how this type of a request would work and would love some insight. Based on the below data, I'm thinking the results will be 0,ドル but would love to see a query to help me get a better grasp of this problem. #1 is the request and I've attached a photo of the table(s) I'm working from. At the very bottom is what I've been able to come up with so far...

Thank you SO much in advance for any and all help.

Given the sample tables below, write a SQL query to show the combined Account MRR by Region for active contracts of at least 6 months with at least one Success engagement.

enter image description here

This is what I've tried to write so far...

FROM Accounts
JOIN Success
ON ID = AccountID
Where Type IS NOT NULL

• Gerard H. Pille: I've written quite a number that were easier.
• Vérace: Hi, and welcome to dba.se! Firstly, please go to dbfiddle.uk and create your tables (DDL) and insert your sample data (DML) and then provide us with your desired result. To make things as simple as possible at the beginning, try to have as few NULLs as possible - you can tackle their complexities later. You require a 3-table JOIN with account_id as your joining field...
• Charlieface: Please do not post images of data, please provide CREATE TABLE and INSERT statements as text instead
• Charlieface: It looks like you probably want EXISTS for the Success join, and you need a filter on ContractLength and Status. Also how does Account_MRR relate to Subsc_MRR, and which of those two are you trying to aggregate?
• T. Koral: Account_MRR and Subsc_MRR are different, so I'm only aggregating the Account_MRR across differing Regions. Does that make sense? Thank you very much for the help!!

Answer 1

First of all you want to create the tables and the sample data. The DBA.SE community normally does this on one of the fiddles available on the internet:

• db<>fiddle
• SQL Fiddle

Then we can figure out what the question is:

Given the sample tables below, write a SQL query to show the combined Account MRR by Region for active contracts of at least 6 months with at least one Success engagement.

• ...show the combined Account MRR... sounds like a good candidate for SUM(<column>)
• ...by Region... sounds like a good candidate for GROUP BY <column>
• ... for active contracts... sounds like a simple WHERE <predicate>
• ...with at least one Success engangement. sounds like a simple JOIN <tablename without the use of either LEFT or RIGHT, which requires there be a match in the involved tables

If we stick this all together we come up with:

SELECT SUM(A.Account_MRR) AS Account_MRR_Sum, Region as Region
FROM Accounts as A 
 JOIN Subscription as S
 ON A.ID = S.AccountID
 AND S.Status = 'Active'
 AND ContractLength >= 6
 JOIN Success as SU
 ON A.ID = SU.AccountID
GROUP BY A.Region;

Which as you suggested, does not return a result.

Account_MRR_Sum | Region
--------------: | :-----

If I comment out the ContractLength >= 6 then I get:

Account_MRR_Sum | Region
--------------: | :-----
 1500.00 | EMEA 

...and if I change the value of the AccountID in the Success table so that there is no matching join, or as you put it: ...with at least one Success engangement., then again no match:

Account_MRR_Sum | Region
--------------: | :-----

The whole fiddle can be found here: db<>fiddle

Reference Reading

• SELECT - GROUP BY- Transact-SQL (Microsoft | SQL Docs)
• SUM (Transact-SQL) (Microsoft | SQL Docs)
• Joins (SQL Server) (Microsoft | SQL Docs)

• T. Koral: John, I have no words for how grateful I am. This is beyond perfect. Your details and explanation make complete sense. Thank you VERY much!!! You’re a life saver