Query data in google spreadsheet and format resulting columns

Question

       A        B      C      D       E     F      G       H          I       
   +--------+------+-------+------+-------+---+---------+------+--------------+
 1 | Select | Name | Price | URL  | Desc  | - | Select  | Name | Price(13.97) |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 2 | x      | n2   | 4.99  | url2 | desc2 | - | x       | n2   | 4.99         |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 3 |        | n3   | 3.99  | url3 | desc3 | - | x       | n6   | 3.99         |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 4 |        | n4   | 7.99  | url4 | desc4 | - | x       | n9   | 4.99         |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 5 |        | n5   | 4.99  | url5 | desc5 | - |         |      |              |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 6 | x      | n6   | 3.99  | url6 | desc6 | - |         |      |              |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 7 |        | n7   | 4.99  | url7 | desc7 | - |         |      |              |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 8 |        | n8   | 3.49  | url8 | desc8 | - |         |      |              |
   +--------+------+-------+------+-------+---+---------+------+--------------+
 9 | x      | n9   | 4.99  | url9 | desc9 | - |         |      |              |
   +--------+------+-------+------+-------+---+---------+------+--------------+

Given the table above A1:E9, I want to generate the result shown in columns G:I

I succeeded getting a similar result (without the parethesis and their value in I1) using in G1 this formula:

=Query(A:E, "SELECT A, B, C WHERE A CONTAINS 'x' ORDER BY B ASC",1)

The question is: How to add the sum of the selected rows to the header of the last column as shown in the example?

Thank you in advance :)

Answer 1

You can break it up into separate parts instead of trying to do it in one query.

In cell G2, use the query:

=Query(A:E, "SELECT A, B, C WHERE A CONTAINS 'x' ORDER BY B ASC label A '', B '', C '' ", 1)

This returns the query without column headers, then you can simply fill in the "name" and "select" column headers into G1 and H1 manually. Then for the cell I1 use

=CONCATENATE("Price (", SUM(I2:I), ")")