Aggregate value for each group by

[mountainclimber]

I'm looking to return different average values for each GICS Sector using this query:

SELECT [GICS Sector], DAvg("GM","tbl0"," [GICS Sector] =  [GICS Sector] AND GM Is Not Null") AS GmMedianByGicsSectorFROM tbl0
WHERE [GICS Sector] =  [GICS Sector]
GROUP BY [GICS Sector];

However, I am getting the same value for each:

GICS Sector GmMedianByGicsSector
Consumer Discretionary 0.469556278179105
Consumer Staples 0.469556278179105
Energy 0.469556278179105


Financials 0.469556278179105
Health Care 0.469556278179105
Industrials 0.469556278179105
Information Technology 0.469556278179105
Materials 0.469556278179105
Telecommunication Services 0.469556278179105
Utilities 0.469556278179105

Can someone help? Thanks.

[ranman256]

why do you have
WHERE [GICS Sector] = [GICS Sector]
?
makes no sense.

[mountainclimber]

why do you have
WHERE [GICS Sector] = [GICS Sector]
?
makes no sense.

Trying stuff to get it to work. Looking for unique averages for reach GICS Sector subset...

[ssanfu]

Air code .... might try

SELECT [GICS Sector], DAvg("GM","tbl0") AS GmMedianByGicsSector
FROM tbl0
WHERE [GM] Is Not Null
GROUP BY [GICS Sector];

or

SELECT [GICS Sector], DAvg("GM","tbl0","[GM] Is Not Null") AS GmMedianByGicsSector
FROM tbl0
GROUP BY [GICS Sector];

Looking at your original query, "DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null")", the criteria evaluates to

[GICS Sector] = [GICS Sector] AND GM Is Not Null
True .......................................... (the number)
So the criteria is "TRUE", which would avg all of the data for each [GICS Sector].


BTW, I would advise not using spaces in object names....just causes you headaches...

[mountainclimber]

Air code .... might try

SELECT [GICS Sector], DAvg("GM","tbl0") AS GmMedianByGicsSector
FROM tbl0
WHERE [GM] Is Not Null
GROUP BY [GICS Sector];

or

SELECT [GICS Sector], DAvg("GM","tbl0","[GM] Is Not Null") AS GmMedianByGicsSector
FROM tbl0
GROUP BY [GICS Sector];

Looking at your original query, "DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null")", the criteria evaluates to

[GICS Sector] = [GICS Sector] AND GM Is Not Null
True .......................................... (the number)
So the criteria is "TRUE", which would avg all of the data for each [GICS Sector].


BTW, I would advise not using spaces in object names....just causes you headaches...

I tried all three suggestions and got the same result as my original post.

[orange]

What is this suppose to mean?

[GICS Sector] =  [GICS Sector]

Where does the value for GICS Sector come from?
And what about GICS Sector?

[mountainclimber]

What is this suppose to mean?

[GICS Sector] =  [GICS Sector]

Where does the value for GICS Sector come from?
And what about GICS Sector?

See my post above: #3.

It comes from the table. It is a field in the table.