Do I need a nested query or is there a way to just multiply?-CodePudding

I am trying to run a query to ultimately have a column that multiplies my count_product_code (count of how many of each product by a specific SKU are sold) and my purchase_size (how many of what item were purchased on each transaction) to get a total_product_sold. Initially when I attempted to do this it did not recognize the count_product_code in the query as a separate name figure 1

So I tried to do a nested query and came out to this result figure 2. What am I doing wrong? Is a nested query even necessary for this? Database: project-1-349215.customer_data.customer_purchases

Figure 1

select count(product_code) as count_product_code, safe_multiply(purchase_size, count_product_code) as total_products_sold, product_code, initcap(product) as product_name, initcap(product_color) as product_color, purchase_size, from project-1-349215.customer_data.customer_purchases where product is not null group by 2,3,4,5 order by count_product_code desc

Figure 2

select safe_multiply(purchase_size, count_product_code) as total_products_sold,

product_code, initcap(product) as product_name, initcap(product_color), purchase_size

( SELECT count(product_code) from project-1-349215.customer_data.customer_purchases group by product_code ) as count_product_code

from project-1-349215.customer_data.customer_purchases where product is not null group by 1,2,3,4,5

CodePudding user response：

You didn't give the details and sample data :( I could guess this is google bigquery but not sure. If it is bigquery then this should run and do what you need:

with counts as (select count(product_code)    as count_product_code,
                       product_code,
                       initcap(product)       as product_name,
                       initcap(product_color) as product_color,
                       purchase_size
                from project-1-349215.customer_data.customer_purchases
                where product is not null
                group by 2, 3, 4, 5)
select count_product_code,
       safe_multiply(purchase_size, count_product_code) as total_products_sold,
       product_code,
       product_name,
       product_color,
       purchase_size
from counts
order by count_product_code desc;