SolvedPrivate

# Vertical Sum For Top 3

Posted on 2013-11-23
35 Views
Hi Experts!

Thanks for reading this.
This is a challenging one for me; if I could give 1000+ I would.
Supposed you've a table with this content:
``````Qid        Value
---------- -----
1            0
2            2
3            1
4            4
5            0
6            0
7            1
8            2
9            0
10           4
``````
Here's how to use the content; you want to get A, B, C, D, and E:
``````A = (Qid of 1) + (Qid of 2) = 0 + 2 = 2
B = (Qid of 3) + (Qid of 4) = 1 + 4 = 5
C = (Qid of 5) + (Qid of 6) = 0 + 0 = 0
D = (Qid of 7) + (Qid of 8) = 1 + 2 = 3
E = (Qid of 9) + (Qid of 10) = 0 + 4 = 4
``````
Ultimately you would like to get the top 3 values.
So, they are: B (5), E (4), and D (3)

The actual logic is like this:

We'll need to do this in LINQ; here's the pseudo code:
``````Q q1  = new Q { Qid=1 , Value = "0" };
Q q2  = new Q { Qid=2 , Value = "2" };
Q q3  = new Q { Qid=3 , Value = "1" };
Q q4  = new Q { Qid=4 , Value = "4" };
Q q5  = new Q { Qid=5 , Value = "0" };
Q q6  = new Q { Qid=6 , Value = "0" };
Q q7  = new Q { Qid=7 , Value = "1" };
Q q8  = new Q { Qid=8 , Value = "2" };
Q q9  = new Q { Qid=9 , Value = "0" };
Q q10 = new Q { Qid=10, Value = "4" };

List<Q> Qs = new List<Q> { q1, q2, q3, q4, q5, q6, q7, q8, q9, q10};

var results = from q in Qs ................
select new
{
A =
B =
C =
D =
E =
};

var Top3 = from r in results ..............
``````

Any ideas?
0
Question by:allanau20
• 3
• 3

LVL 52

Expert Comment

ID: 39671321
Try this:
``````            List<Q> list = new List<Q>
{
new Q { Qid = 1, Value = 0},
new Q { Qid = 2, Value = 2},
new Q { Qid = 3, Value = 1},
new Q { Qid = 4, Value = 4},
new Q { Qid = 5, Value = 0},
new Q { Qid = 6, Value = 0},
new Q { Qid = 7, Value = 1},
new Q { Qid = 8, Value = 2},
new Q { Qid = 9, Value = 0},
new Q { Qid = 10, Value = 4}
};

var query = from x in list
join y in list on (x.Qid + 1) equals y.Qid
where x.Qid % 2 == 1
select (x.Value + y.Value);

var results = (from x in query
orderby x descending
select x).Take(3);
``````
0

LVL 5

Author Comment

ID: 39671940
Thanks pretty clever carl_tawn. The top 3 is correct; but we're unable to determine from which of 'A-E' is it associated with. In our example, the top 3 are: B = 5, E = 4, and D = 3

Can you please take a look at my 'query'? Trying to add three Qids to make this:
A= Qid1 + Qid17 + Qid33, B=Qid2 + Qid18 + Qid34, all the way to P.
``````    List<Q> list = new List<Q>
{
new Q { Qid = 1, Value = 0},
new Q { Qid = 2, Value = 2},
new Q { Qid = 3, Value = 1},
new Q { Qid = 4, Value = 4},
new Q { Qid = 5, Value = 0},
new Q { Qid = 6, Value = 0},
new Q { Qid = 7, Value = 1},
new Q { Qid = 8, Value = 2},
new Q { Qid = 9, Value = 0},
new Q { Qid = 10, Value = 4},
new Q { Qid = 11, Value = 0},
new Q { Qid = 12, Value = 2},
new Q { Qid = 13, Value = 1},
new Q { Qid = 14, Value = 4},
new Q { Qid = 15, Value = 0},
new Q { Qid = 16, Value = 0},
new Q { Qid = 17, Value = 1},
new Q { Qid = 18, Value = 2},
new Q { Qid = 19, Value = 0},
new Q { Qid = 20, Value = 4},
new Q { Qid = 21, Value = 0},
new Q { Qid = 22, Value = 2},
new Q { Qid = 23, Value = 1},
new Q { Qid = 24, Value = 4},
new Q { Qid = 25, Value = 0},
new Q { Qid = 26, Value = 0},
new Q { Qid = 27, Value = 1},
new Q { Qid = 28, Value = 2},
new Q { Qid = 29, Value = 0},
new Q { Qid = 30, Value = 4},
new Q { Qid = 31, Value = 0},
new Q { Qid = 32, Value = 2},
new Q { Qid = 33, Value = 1},
new Q { Qid = 34, Value = 4},
new Q { Qid = 35, Value = 0},
new Q { Qid = 36, Value = 0},
new Q { Qid = 37, Value = 1},
new Q { Qid = 38, Value = 2},
new Q { Qid = 39, Value = 0},
new Q { Qid = 40, Value = 4},
new Q { Qid = 41, Value = 0},
new Q { Qid = 42, Value = 2},
new Q { Qid = 43, Value = 1},
new Q { Qid = 44, Value = 4},
new Q { Qid = 45, Value = 0},
new Q { Qid = 46, Value = 0},
new Q { Qid = 47, Value = 1},
new Q { Qid = 48, Value = 2}
};

var query = from x in list
join y in list on (x.Qid + 16) equals y.Qid
join z in list on (x.Qid + 32) equals z.Qid
select (x.Value + y.Value + z.Value);

var results = (from x in query
orderby x descending
select x).Take(3);
``````

Is there a way to tell from which logic (A-P) the top 3 came from?
0

LVL 52

Expert Comment

ID: 39672832
This should do what you want:
``````var query = (from x in list
join y in list on (x.Qid + 16) equals y.Qid
join z in list on (x.Qid + 32) equals z.Qid
where x.Qid <= 16
select x.Value + y.Value + z.Value)
.Select((item, index) => new { Letter = (char)(65 + index), Value = (int)item });

var results = (from x in query
orderby x.Value descending
select x).Take(3);

foreach(var item in results)
{
Console.WriteLine(item.Letter + "=" + item.Value);
}
``````
0

LVL 5

Author Comment

ID: 39673627
Thanks carl_tawn; came across this in results:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType5`2[System.Char,System.Int32]] Select[Int32,<>f__AnonymousType5`2](System.Linq.IQueryable`1[System.Int32], System.Linq.Expressions.Expression`1[System.Func`3[System.Int32,System.Int32,<>f__AnonymousType5`2[System.Char,System.Int32]]])' method, and this method cannot be translated into a store expression.

The example above is using local values, but what I'm doing is querying two tables in a database:
``````var answered = from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new
{
Qid = a.QuestionID,
Value = v.ScaleValue
};

var query = (from x in answered
join y in answered on (x.Qid + 16) equals y.Qid
join z in answered on (x.Qid + 32) equals z.Qid
where x.Qid <= 16
select x.Value + y.Value + z.Value)
.Select((item, index) => new { Letter = (char)(65 + index), Value = item });

var results = (from x in query
orderby x.Value descending
select x).Take(3);
``````

Qid and Value are Int data type.

I tried and cannot get it to work. Thanks again for your help!
0

LVL 52

Accepted Solution

Carl Tawn earned 500 total points
ID: 39674217
You need to detach your result set from the Entity view of the model. Assuming you still have your Q class kicking around, you can change the line:
``````var answered = from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new
{
Qid = a.QuestionID,
Value = v.ScaleValue
};
``````
To:
``````List<Q> answered = (from a in db.tbl01
join v in db.tbl01
on a.ScaleID equals v.tScaleID
where a.UserID == myUser.UserID
select new Q
{
Qid = a.QuestionID,
Value = v.ScaleValue
}).ToList<Q>();
``````
And the rest should then work as before.
0

LVL 5

Author Closing Comment

ID: 39676311
Thank you carl_tawn; I saved time and learned something new from you!
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

#### 743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!