very simple amateur question. Build a table then do group_concat



  • I am very new to SQL and don't have much intuition for all this. I have lots of coding experience but nothing in databases.

    I have two scriptthat work in isolation, but I don't know how to stitch them together to do this all at once.

    The first script:

    SELECT *
      FROM PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTES bq 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.PRODUCT p ON bq.ProductId = p.Id 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTEFEES F on F.ApplicationQuoteId = bq.Id 
    LEFT JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.COVERAGESECTIONTYPE CST ON F.CoverageSectionTypeID = CST.SECTIONID
    

    This produces a table (is that the right word?). Suppose I could save that table as 'appQuote'. I then want to run this script on it:

    select ApplicationId, 
                group_concat(YEARLYPAYAMOUNT) allPremium,
                group_concat(SECTIONID) allSection,
                group_concat(SHORTNAME) allSectionName,
                from appQuote group by ApplicationId
    

    For storage reasons I don't want to actually save the table from the first script, I just want to get the result from the first script and immediately apply the second script to it.

    This is very basic, so any guidance and intuition would help.



  • You want to learn about WITH. You can combine the two queries by adding

    WITH appQuote as () . Whenever possible, especially with automated functions, specify the column names rather than using select *. Using * has a high chance of breaking if your table's columns are ever modified.




Suggested Topics

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