Summarize (sum of this minus sum of that?)

Get Help
1

Greetings,

I've made use of the Summarize 'sum' feature so I get a number, a total for a certain column.
It's the line items costs total. But really, what I want displayed is that total minus the discounts column total.

How can I do that?

Thank you!!!

Antoine

2

Hi @Antoine
Custom Expressions: https://www.metabase.com/docs/latest/users-guide/expressions.html
Sum([Total]) - Sum([Discount])

3

Hi Flamber,

I'm going through a Udemy course on SQL for obvious reasons here.
It's been far too long since I did SQL! (20+ years).

May I ask given this question, what would work?

I tried SELECT sum("company_name"."company_name_orderitem"."total_cost") - sum("company_name"."company_name_po"."discount")

But that likely will elicit the same good laugh in you that it did in the engine if it was conscious :sweat_smile: I understand that I may be 'reaching' a little here in my ask for help and I won't go this far in the future (I have someone starting out that'll have the required skills).

Thank you kindly,

Antoine

SELECT sum("company_name"."company_name_orderitem"."total_cost") AS "sum"
FROM "company_name"."company_name_orderitem"
LEFT JOIN "company_name"."company_name_po" "company_name_po" ON "company_name"."company_name_orderitem"."purchase_order_id" = "company_name_po"."company_name_po" LEFT JOIN "company_name"."company_name_order" "company_name_order" ON "company_name"."company_name_orderitem"."ordernum_id" = "company_name_order"."num" LEFT JOIN "company_name"."company_name_department" "company_name_department" ON "company_name_order"."department_id" = "company_name_department"."id"
WHERE ("company_name"."company_name_orderitem"."database_name" = 'database_name'
   AND "company_name_po"."database_name" = 'database_name' AND "company_name_order"."database_name" = 'database_name' AND "company_name_department"."database_name" = 'database_name' AND "company_name_department"."name" = 'some name')
4

@Antoine I don't understand. If your question is regard Custom Expressions, then please read the documentation page once more. If your question is regarding SQL, then I don't know what the question is, but you can likely find better help in a forum dedicated the the database type you're using or stackoverflow.com

1 Like
5

I'm inferring the database schema from the table names, so apologies if this is nonsense:

Each PO has multiple lines, each with an item.

Is the po discount a percentage or a value?
If it's a percentage, you need to do something like SUM(total_cost * discount)
If it's a value, you need to do something clever dividing the discount by the count of the number of lines on the order (would be easier to do as 2 queries).

1 Like