CTE or sub query not working

General Discussions - English
1 
SELECT id,m_id,b_id,status,dp.price as dp_price,SUM(IF(SUBSTRING_INDEX(dp.price, '$', -1),SUBSTRING_INDEX(dp.price, '$', -1),0)) AS total_dp_price FROM lists WHERE  
            status IN ('NEEDS_REVIEW') AND 
          l_id = 692381349893783561 AND GROUP BY b_id
           ORDER BY updated_at DESC  FACET m_id,b_id,status,dp.price as dp_price ORDER BY COUNT(*) DESC ; SELECT COUNT(*) AS count FROM lists;

this is my working query but it affects the entire data due to GROUP BY I can’t get data for the same value of,m_id,b_id, and status.
I want to apply a sub-query for the sum of total_dp_price

I can do in this manner but getting error
`` SELECT
l.id,
l.marketplace_id,
l.brand_id,
l.status,
l.data_points.price AS data_point_price,
dp.total_data_point_price
FROM listings l,
JOIN (
SELECT
brand_id,
SUM(
IF(
SUBSTRING_INDEX(data_points.price, ‘$’, -1) REGEXP ‘^[0-9.]+$’,
CAST(SUBSTRING_INDEX(data_points.price, ‘$’, -1) AS DECIMAL(10, 2)),
0
)
) AS total_data_point_price
FROM listings
WHERE
status = ‘NEEDS_REVIEW’
AND licensor_id = 692381349893783561
GROUP BY brand_id
) dp ON l.brand_id = dp.brand_id
WHERE
l.status = ‘NEEDS_REVIEW’
AND l.licensor_id = 692381349893783561
ORDER BY
dp.total_data_point_price DESC, l.updated_at DESC
LIMIT 10;`

this kind of error
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 1064 (42000): P01: syntax error, unexpected identifier, expecting $end near 'l,

Any Help Would Be Appreciated:

Your assistance in resolving this issue or providing guidance on how to achieve sub-query with this query

2

seems like a daemon crash it could be better to check your indexes with indextool to make sure these are valid indexes then create ticket at Github there to upload config, indexes and queries these reproduce this crash locally.

3

Thank you for assistant I shared the config file & indexing options in the git issue here is the link of the git issue:

Can you please further guide me on what’s an actual issue