Migration query works but migration fails due to sourceid not being found
The relevent part of the error message is "'drupal_8.map.sourceid1' isn't in GROUP BY". Let's look at the query as executed:

SELECT sca.price AS price,
map.sourceid1 AS migrate_map_sourceid1, map.source_row_status AS migrate_map_source_row_status
...
GROUP BY price


SQL requires that all columns being selected be part of the GROUP BY. The SqlBase class has determined that your source query can be joined directly to the migration's map table, and thus performs the join as an optimization, adding the map table fields to the columns returned. However, it doesn't add them to the GROUP BY. There's an issue for that: https://www.drupal.org/node/2844595. For now, you can set

ignore_map: true


in your source plugin's configuration to prevent the join and it will work.