{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.138-89.102.amzn1.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"googleanalytics",
"h2",
"mysql"
],
"hosting-env": "elastic-beanstalk",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "9.6.20"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-06-15",
"tag": "v0.39.4",
"branch": "release-x.39.x",
"hash": "f538050"
},
"settings": {
"report-timezone": "UTC"
}
}
}
The query generated by the query builder results in this error. The source DB is Mysql5.7. Possibly an issue with the generation of the derived table used in the group by?
Unknown column 'Question 55.date_ordered' in 'field list'
SELECT
`source`.`amount` AS `amount`,
`source`.`currency_id` AS `currency_id`,
`source`.`date_created` AS `date_created`,
`source`.`discount` AS `discount`,
`source`.`revenue` AS `revenue`,
`source`.`shipping` AS `shipping`,
`source`.`tax` AS `tax`,
`source`.`transaction_id` AS `transaction_id`,
`source`.`type` AS `type`,
`source`.`prv` AS `prv`,
`source`.`frv` AS `frv`,
`source`.`Question 55__date_ordered` AS `Question 55__date_ordered`,
`source`.`count` AS `count`,
`source`.`sum` AS `sum`,
`source`.`sum_2` AS `sum_2`
FROM
(
SELECT
`transactions`.`currency_id` AS `currency_id`,
`transactions`.`date_created` AS `date_created`,
`transactions`.`provider_status` AS `provider_status`,
`transactions`.`type` AS `type`,
`transactions`.`revenue` AS `revenue`,
`transactions`.`discount` AS `discount`,
`transactions`.`other` AS `other`,
`transactions`.`shipping` AS `shipping`,
`transactions`.`amount` AS `amount`,
`transactions`.`tax` AS `tax`,
`transactions`.`transaction_id` AS `transaction_id`,
(
(`transactions`.`revenue` + `transactions`.`discount` + `transactions`.`other`) * 0.8
)
AS `prv`,
(
`transactions`.`shipping` * 0.8
)
AS `frv`,
`Question 55`.`date_ordered` AS `Question 55__date_ordered`,
`Question 55`.`count` AS `count`,
`Question 55`.`sum` AS `sum`,
`Question 55`.`sum_2` AS `sum_2`
FROM
`transactions`
LEFT JOIN
(
SELECT
date(`source`.`Orders__date_ordered`) AS `Orders__date_ordered`,
count(*) AS `count`,
sum(`source`.`prv`) AS `sum`,
sum(`source`.`frv`) AS `sum_2`
FROM
(
SELECT
`transactions`.`provider_status` AS `provider_status`,
`transactions`.`type` AS `type`,
`transactions`.`date_created` AS `date_created`,
`transactions`.`currency_id` AS `currency_id`,
`transactions`.`revenue` AS `revenue`,
`transactions`.`discount` AS `discount`,
`transactions`.`other` AS `other`,
`transactions`.`shipping` AS `shipping`,
(
(`transactions`.`revenue` + `transactions`.`discount` + `transactions`.`other`) * 0.8
)
AS `prv`,
(
`transactions`.`shipping` * 0.8
)
AS `frv`,
`Orders`.`date_ordered` AS `Orders__date_ordered`
FROM
`transactions`
LEFT JOIN
`order_x_transactions` `oxt`
ON `transactions`.`transaction_id` = `oxt`.`transaction_id`
LEFT JOIN
`orders` `Orders`
ON `oxt`.`order_id` = `Orders`.`order_id`
)
`source`
WHERE
(
`source`.`provider_status` = 'completed'
AND `source`.`type` = 'refund'
AND `source`.`date_created` >= date(date_add(now(6), INTERVAL - 30 day))
AND `source`.`date_created` < date(now(6))
AND `source`.`currency_id` = 2
)
GROUP BY
date(`source`.`Orders__date_ordered`)
)
`Question 55`
ON str_to_date(concat(date_format(`transactions`.`date_created`, '%Y-%m'), '-01'), '%Y-%m-%d') = str_to_date(concat(date_format(`Question 55`.`date_created`, '%Y-%m'), '-01'), '%Y-%m-%d')
)
`source`
WHERE
(
`source`.`currency_id` = 2
AND `source`.`date_created` >= date(date_add(now(6), INTERVAL - 30 day))
AND `source`.`date_created` < date(now(6))
AND `source`.`provider_status` = 'completed'
AND `source`.`type` = 'payment'
)
LIMIT 1048575