ON map_account_affiliates.id = chargebacks.map_account_affiliate_id ON map_account_affiliates.id = refunds.map_account_affiliate_idįILTER (WHERE t.transaction_type = 7) AS num_chargebacks,įILTER (WHERE t.transaction_type = 8) AS num_chargebacks_won,įILTER (WHERE t.transaction_type = 7), 0)įILTER (WHERE t.transaction_type = 8), 0) ON map_account_affiliates.id = sales.map_account_affiliate_idįILTER (WHERE t.transaction_type = 5) AS num_full_refunds,įILTER (WHERE t.transaction_type = 55) AS num_part_refunds, T.map_account_affiliate_id AS map_account_affiliate_id,įILTER (WHERE t.transaction_type = 22) AS num_scheduled_sales,įILTER (WHERE t.transaction_type = 22) AS gross_scheduled_sales,įILTER (WHERE t.front_end = TRUE) AS num_fe_sales,įILTER (WHERE t.front_end = TRUE) AS gross_fe_sales (chargebacks.num_chargebacks) :: FLOAT / nullif(sales.num_sales, 0) * 100.0 AS percent_chargebacks, ) :: FLOAT / nullif(sales.num_sales, 0) * 100.0 AS percent_refunds,ĬOALESCE(chargebacks.num_chargebacks_won, 0)ĬOALESCE(chargebacks.total_chargebacks, 0)ĬOALESCE(chargebacks.total_chargebacks :: FLOAT / nullif(oss_sales, 0) Map_account_affiliates.display_name AS resource_name,ĬOALESCE(oss_sales :: FLOAT / nullif(sales.num_fe_sales :: FLOAT, 0)ĬOALESCE(sales.num_fe_sales / nullif(((SUM(sales.num_fe_sales)ĬOALESCE((oss_fe_sales :: FLOAT / nullif(sales.num_fe_sales :: FLOAT, 0)), 0) AS avg_fe_sale_amt,ĬOALESCE(refunds.total_refunds :: FLOAT / nullif(oss_sales, 0) Map_account_affiliates.id AS resource_id, INNER JOIN line_items ON line_items.id = children.line_item_id INNER JOIN line_items ON line_items.id = transactions.line_item_id Orders.t2_affiliate_tracking_id AS t2_affiliate_tracking_id, Orders.t1_affiliate_tracking_id AS t1_affiliate_tracking_id, Orders.map_account_affiliate_id AS map_account_affiliate_id, WITH RECURSIVE transactions_with_children AS ( I tried just simplifying the query for the purposes of the question, but maybe its easier to look at the original. This seems like a hacky way to do it, but i'm not all that great with SQL either. I assume the join select above would include a null field if transactions_with_children.map_account_affiliate_id had any null values right? ) ids ON map_account_affiliates.id = ids.map_account_affiliate_id I guess I could maybe create an RIGHT JOIN sub select where I am pulling records from transactions_with_children and grouping by. So the problem with doing a RIGHT JOIN on transactions_with_children (I think) is that I would get tons of duplicates from transactions_with_children table. I then join on the common table_A_id that each of the records from the WITH may or may not have. Each of the LEFT JOINs that i'm doing using this table just aggregate data from it. The recursive WITH tables are all scoped records from a large table. INNER JOIN transactions_with_children on transactions_with_children.table_A_id = table_A.id a null row for all transactions_with_children that don't have a table_A_id Limit table_A to only records with data from transactions_with_children, but I also need to include View_C.aggregate_col1, view_C.aggregate_col2 View_B.aggregate_col1, view_B.aggregate_col2, INNER JOIN transactions_with_children parents ON children.parent_transaction_id = parents.id WHERE transactions.parent_transaction_id IS NULL How can I select all rows from table_A and then join on results from other tables, but include a NULL row to show aggregate values for those other tables that don't have a table_A_id? WITH RECURSIVE transactions_with_children AS (
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |