Vertical Sum For Top 3

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

Open in new window

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

Open in new window

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:
questions.PNG
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 ..............

Open in new window


Any ideas?
LVL 5
allanau20Asked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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
                };

Open in new window

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>();

Open in new window

And the rest should then work as before.
0
 
Carl TawnSystems and Integration DeveloperCommented:
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);

Open in new window

0
 
allanau20Author Commented:
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:
questionA= 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);

Open in new window


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

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Carl TawnSystems and Integration DeveloperCommented:
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);
}

Open in new window

0
 
allanau20Author Commented:
Thanks carl_tawn; came across this in results:

error
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);

Open in new window


Qid and Value are Int data type.

I tried and cannot get it to work. Thanks again for your help!
0
 
allanau20Author Commented:
Thank you carl_tawn; I saved time and learned something new from you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.