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



  • 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



  • 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:

    • https://dbfiddle.uk
    • https://sqlfiddle.com/

    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()
    • ...by Region... sounds like a good candidate for GROUP BY
    • ... for active contracts... sounds like a simple WHERE
    • ...with at least one Success engangement. sounds like a simple JOIN 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: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ce26f96435fdc273b89f95fb483a8ca7

    Reference Reading

    • https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15 (Microsoft | SQL Docs)
    • https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver15 (Microsoft | SQL Docs)
    • https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 (Microsoft | SQL Docs)

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2