SolvedPrivate

Vertical Sum For Top 3

Posted on 2013-11-23
6
38 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:allanau20
  • 3
  • 3
6 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
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);

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
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:
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
 
LVL 52

Expert Comment

by:Carl Tawn
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);
}

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 5

Author Comment

by:allanau20
ID: 39673627
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
 
LVL 52

Accepted Solution

by:
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
                };

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
 
LVL 5

Author Closing Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

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

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

Join & Ask a Question