CODEWITHSUNDEEP
sqlgoogle-bigquery
Zyre Soriano
Zyre Soriano

Reputation: 605

How to add a column with data based on another column

I have a BQ table that looks look this:

Sample Data

and I want to add a column that divides the shipping_handling based on the number of rows under the same invoice, in this case since there are 5 entries under invoice J513183, it will be 461.71/5 = 92.34

which would look like this:

Expected Output

tried adding this code "shipping_handling/count(invoice) as shipping" but it's not working. I hope anyone can help me find a solution on this google big query sql

Upvotes: 0

Views: 64

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL

#standardSQL
SELECT *,
  ROUND(shipping_handling / (COUNT(*) OVER(PARTITION BY invoice)), 2) AS shipping
FROM `project.dataset.table`

Upvotes: 1

ASisic
ASisic

Reputation: 109

One possible solution would be to create temp table with count of groups and than use it in your query

-- DROP TABLE IF EXISTS
IF OBJECT_ID(N'tempdb..#tmpInvoice') IS NOT NULL
BEGIN
DROP TABLE #tmpInvoice
END
        
-- GROUP INVOICES AND INSERT INTO TEMP TABLE
SELECT invoice as invoiceId, COUNT(invoice) invoiceCount
INTO #tmpInvoice
FROM BQ GROUP BY invoice
        
-- GET SHIPPING WITH ADDITIONAL SELECT
SELECT invoice_date,
        invoice,
        description,
        shipping_handling,
        avg_line_total,
        cast((shipping_handling/(SELECT invoiceCount from #tmpInvoice where invoiceId = invoice)) as numeric(10,2)) Shipping
FROM BQ
        
-- GET SHIPPING WITH INNER JOIN
SELECT bq.invoice_date,
        bq.invoice,
        bq.description,
        bq.shipping_handling,
        bq.avg_line_total,
        cast((bq.shipping_handling/ti.invoiceCount) as numeric(10,2)) Shipping
FROM BQ bq
INNER JOIN #tmpInvoice ti on bq.invoice = ti.invoiceId

Upvotes: 0

user330315
user330315

Reputation:

You can use a window function:

select invoice_date, 
       invoice, 
       description,
       shipping_handling,
       avg_line_total, 
       shipping_handling / count(*) partition by (invoice) as shipping
from the_table;

Depending on your DBMS, you might need to cast one of the values in the division to a numeric data type otherwise it might use integer division which does not yield decimal digits.

Upvotes: 0

Related Questions