Archive

Archive

CTE not running for dataset self join

Member

I am trying to combine a dataset with itself to calculate two flavors of one column. I could not figure out why it should npt work. I apreciate your help as I am new to mysql syntax

 
WITH
cte1 AS (SELECT `period`, `FY/Qtr`, Count(`contract number`) AS `Total`, `PFA Type` FROM cm_reports_dates Group by `period`, `FY/Qtr`, `PFA Type`),
cte2 AS (SELECT COUNT(`contract number`) AS `Frequency`, `pfa Type` FROM cm_reports_dates where `interval` < 30 group by `PFA Type`)
SELECT `period`, `FY/Qtr`, Total, cte1.`PFA Type`, Frequency FROM cte1 join cte2
WHERE cte1.`pfa type` = cte2.`pfa type`;

 

Thanks

Sam

 

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Comments

  • Coach

    What is the erro message you're seeing?

    It looks good to me except maybe your join.

    Try 

    ...

    FROM cte1, cte2
    WHERE cte1.`pfa type` = cte2.`pfa type`;

     

    OR

    ...

    FROM cte1 join cte2
    ON cte1.`pfa type` = cte2.`pfa type`;

     

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Member

    I am trying to use cte since I am operating one field from one dataset. Here is the error message I am getting attached.

     

    Thanks

     

     

     

  • Coach

    This might be a problem with the way Domo has implemented mySQL CTE functionality.

    Instead you could try listing your cte1 and cte2 as subqueries in your FROM clause.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In