Query using Dsum challenge for the experts 1

[awingnut]

Here's a challenge for you query experts. It is considered cheating if you use code. Given the following query columns:

Name Value Total

There will be duplicates of column "Name". The challenge is to come up with the expression for the "Total" column that will add the numbers in the "Value" column sumed by "Name". For example:

Bob    5   8
Dave   2   5
Bob    1   8
Sam    6   6
Ed     4   4
Dave   3   5
Bob    2   8

Although it doesn't really matter, for the sake of this challenge assume, "Name" comes from "MyNames" table and "Value" comes from a related table called "MyValues".

What is the expression for the "Total" column that will yield the above results? TIA.

 

[markgrizzle]

Select MyNames.Name, Sum(MyValues.Value) As SumOfValue
From MyNames Inner Join MyValues
On MyNames.Name = MyValues.Name
Group By MyNames.Name;

 

[zerosandones]

The first reply was a good try, but, nonetheless incorrect.

If you read the post carefully, and examine the data structures carefully, you will discover that i) the poster has asked that the results be grouped by name and value "and" include a sum of values for each name and ii) that the MyNames table is not required to produce the requested results.

Two copies of the MyValues table does the trick ... here's the SQL:

SELECT MyValues.Name, MyValues.Value, Sum(MyValues_1.Value) AS Total
FROM MyValues INNER JOIN MyValues AS MyValues_1 ON MyValues.Name = MyValues_1.Name
GROUP BY MyValues.Name, MyValues.Value;

00001111s

 

[awingnut]

Thanks for the quick reply but no cigar, yet.

I'm getting an error that says the expression "Value" is not part of an aggregate function. I also made one change to your suggestion since I need that column for something else. I don't think that is what it is complaining about however.

Select MyNames.Name,MyValues.Value, Sum(MyValues.Value) As SumOfValue
From MyNames Inner Join MyValues
On MyNames.Name = MyValues.Name
Group By MyNames.Name;

 

[awingnut]

After some playing around I got rid of the aggregate error by adding "Value" to the 'Group By' clause. The query now looks like:

Select MyNames.Name,MyValues.Value, Sum(MyValues.Value) As SumOfValue
From MyNames Inner Join MyValues
On MyNames.Name = MyValues.Name
Group By MyNames.Name,MyValues.Value;

Unfortunately this does not sum by Name but by row. All it does is effectively duplicate the "Value" column.

 

[TelcoBT]

I think you have made an error in keying,

Note the Sum is MyValues_1 not MyValues in the original

SELECT
MyValues.Name, MyValues.Value, Sum(MyValues_1.Value) AS Total
FROM MyValues INNER JOIN MyValues AS MyValues_1 ON MyValues.Name = MyValues_1.Name
GROUP BY MyValues.Name, MyValues.Value;

I think you need to edit yours to

Select MyNames.Name,MyNames.Value, Sum(MyValues.Value) As SumOfValue
From MyNames Inner Join MyValues
On MyNames.Name = MyValues.Name
Group By MyNames.Name, MyNames.Value;

 

[awingnut]

I think one of use misunderstands. There is no "Value" in "MyNames". The relationship between "MyNames" and "MyValues" is "Name".

 

[awingnut]

I finally got someone to help me with this. To close this out, here is the working SQL statement:

SELECT MyValues.Name, Sum(MyValues.Value) AS Total FROM MyValues GROUP BY MyValues.Name ORDER BY Val( Sum(MyValues.Value)) DESC;

I was hung up on 'MyNames.Name' but since that is the relationship for 'MyValues' and 'MyValues' contains 'Name', 'MyNames' was unnecessary in the expression.