Confluence - Table Transformer Macro - Need help in adding rows based on common value

[Karthika_Natarajan]

Hi, 

I have a table like this. 

I want to group the Items and calculate the total price for the total quantity. 

Items    Price   quantity

item 1     3         10

item 1     3           6

item 2     5           5

item 2     5         10

I want the Result table like this.  I tried to sum it using Group By but it doesn't help. Kindly help me with the query.

Items     Price     quantity    Total Price

Item1       3            16             48

Item2       5            15             75

[Stiltsoft support · Accepted solution]

Hi @Karthika_Natarajan ,

Please try the following SQL query:

SELECT 'Items', 'Price', 'Total Quantity',
('Price' * 'Total Quantity') AS 'Total Price'
FROM
(SELECT *,
SUM('Quantity') AS 'Total Quantity'
FROM T*
GROUP BY 'Items', 'Price')

Hope this helps your case.

[Karthika_Natarajan]

Yeah, this works fine. Thanks

[Stiltsoft support]

Hi @Karthika_Natarajan ,

You may also the this query that looks more neat:

SELECT 'Items', 'Price',
SUM('Quantity') AS 'Total Quantity',
SUM('Quantity' * 'Price') as 'Total Price'
FROM T* GROUP BY 'Items', 'Price'

The result will be the same.