CTE not running for dataset self join

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

Find more posts tagged with

Comments

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`;

...

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

somer

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

dojo.png

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.