Cross tab query with averages?

[DKS]

Hi,

I have a table with following 3 columns

Date
Value 1
Value 2

Value 1 and Value 2 are numeric fields (zero decimals). For a given date
there can be many records.

I want a query that gives me values grouped by DATE field. Thus one line
for each Date. And the second column in the query I need the sum of Value 1
fields divided by the sum of Value 2 fields.

E.g.

If for 1/1/2009 I have following data
1/1/2009 10 4
1/1/2009 15 5
1/1/2009 10 7
1/1/2009 3 0

Then I need in the output of the query

1/1/2009 2.375 (=38/16)

All help appreciated.

 

[Lynn Trapp]

Give this a try:

SELECT EntryDate, Sum1/Sum2 AS Result
FROM (SELECT V.EntryDate AS EntryDate, Sum(V.Value1) AS Sum1, Sum(V.Value2)
AS Sum2
FROM [Values] AS V
GROUP BY V.EntryDate);

 

[Krzysztof Naworyta]

Select
Date,
Sum(Value1)/Sum(Value2) As MyAvg
From
MyTable
Group By
Date

--
KN

DKS wrote:

| I have a table with following 3 columns
|
| Date
| Value 1
| Value 2
|
| Value 1 and Value 2 are numeric fields (zero decimals). For a given
| date there can be many records.
|
| I want a query that gives me values grouped by DATE field. Thus one
| line for each Date. And the second column in the query I need the
| sum of Value 1 fields divided by the sum of Value 2 fields.
|
| E.g.
|
| If for 1/1/2009 I have following data
| 1/1/2009 10 4
| 1/1/2009 15 5
| 1/1/2009 10 7
| 1/1/2009 3 0
|
| Then I need in the output of the query
|
| 1/1/2009 2.375 (=38/16)
|
| All help appreciated.

 

[DKS]

Thanks. This works for me, and this is what I wanted.

Give this a try:

SELECT EntryDate, Sum1/Sum2 AS Result
FROM (SELECT V.EntryDate AS EntryDate, Sum(V.Value1) AS Sum1, Sum(V.Value2)
AS Sum2
FROM [Values] AS V
GROUP BY V.EntryDate);

--
Lynn Trapp

Hi,

I have a table with following 3 columns

Date
Value 1
Value 2

Value 1 and Value 2 are numeric fields (zero decimals). For a given date
there can be many records.

I want a query that gives me values grouped by DATE field. Thus one line
for each Date. And the second column in the query I need the sum of Value 1
fields divided by the sum of Value 2 fields.

E.g.

If for 1/1/2009 I have following data
1/1/2009 10 4
1/1/2009 15 5
1/1/2009 10 7
1/1/2009 3 0

Then I need in the output of the query

1/1/2009 2.375 (=38/16)

All help appreciated.

 

[DKS]

Thanks. This is even simpler then the previously suggested solution.