Output a normalized column with a calculated field in QUERY

Question

In Google Spreadsheet, I have the expression:

=QUERY(database; "select b,c, where a='anyone-condition' order by c desc";-1)

The column a,b are strings and c is a number.
How can I include in this query one calculated field, c/sum(c)?

Answer 1

Although Ed Nelson is perfectly right, you can be creative with multiple nested Queries and get desire result without an extra column.

I have build simple example that will show you the way

Data

Solution 1 - simpler but with ugly column name

 =QUERY(
  A1:C3,"select A,B,C/"&QUERY(
  QUERY(A1:C3,"select sum(C)"),"select * offset 1",0)&"" ,1
 )

Solution 1.1 - more complex but with custom column name

=QUERY(
 A1:C3,"select A,B,C/"&QUERY(
  QUERY(A1:C3,"select sum(C)"),"select * offset 1",0)&
 " label C/"&QUERY(
  QUERY(A1:C3,"select sum(C)"),"select * offset 1",0)&
 " 'C/sum'",1
)

Finals:

You can go further and add formating to last column as well

Link to working copy

For your specific example probably code should be like this:

=query(database; "select b,"&query(query(database; "select sum(c), where a='anyone-condition' "),"select * offset 1",0)&" where a='anyone-condition' order by c desc";-1)

But, it should be tested against real data

Is that serves your needs?

Answer 2

You can't do a sum without doing a group which you don't want. You can add a column D to your sheet to sum. Try =sum($C$2:$C) in D and copy down. Then this will work:

=query(database, "select B,C/D where A='anyone-condition' order by C desc label C/D ''",-1)

Answer 3

The problem is with the Google Spreadsheet 'kernel', not with my coding. Look the scream that I printed

https://drive.google.com/file/d/1yJbcfVZ1tDmW8WoG224PALRIwyEbX9LH/view?usp=sharing