none
How to count columns having a value? RRS feed

  • Question

  • Sir,

    I am developing an ASP.NET MVC application using Entity Frameworks. Please help me find a solution to the following problem.

    I have two table as under:

    Table 1: Employee(Id, Name)

    Table 2: Docs(Id, EmpId, Image1, Image2, Image3)

    The above tables have data as under:

    Table1:

    Id       Name

    1         Donald

    2         Smith

    3         John

    Table 2:

    Id         EmpId           Image1            Image2             Image3

    1             2                    Yes                No                    Yes

    2             1                     No                No                     No

    3             3                     Yes               Yes                    Yes

    I need to display List of Employees with the number of images as shown below:

    Name of Employee       Documents

    Donald                                0

    Smith                                  2

    John                                    3

    Warm Regards,

    Arun

                   

    Friday, September 22, 2017 5:12 PM

All replies

  • You can replace your 'Yes' with int values and put them in a CTE. Then you can count them in your Select statement.

    Will this work for you ?


    ;WITH X 
    AS
    (
    SELECT EmpID, CASE WHEN image1 = 'yes' THEN 1 ELSE 0 END as Image1 , CASE WHEN image2 = 'yes' THEN 1 ELSE 0 END AS Image2,
    CASE WHEN image3 = 'yes' THEN 1 ELSE 0 END AS Image3 FROM #test
    )
    SELECT empID, Image1+Image2+Image3 as Cnt FROM X


    Karun R

    Friday, September 22, 2017 5:22 PM
  • Dear Karun,

    Thanks for replying. Could you tell how to do same using LINQ to Entities?

    Warm Regards,

    Arun

    Saturday, September 23, 2017 6:11 AM
  • Hi ArunKhatri

    Thank you for posting here.

    According to your description, I made a demo which can implements your description, please take a reference.

    namespace App8TestBLE
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable tab1 = new DataTable();
                DataTable tab2 = new DataTable();
    
                tab1.Columns.Add("id", typeof(int));
                tab1.Columns.Add("Name", typeof(string));
                tab1.Rows.Add(1, "Donald");
                tab1.Rows.Add(2, "Smith");
                tab1.Rows.Add(3, "John");
    
                tab2.Columns.Add("Id", typeof(int));
                tab2.Columns.Add("EmpId", typeof(int));
                tab2.Columns.Add("Image1", typeof(string));
                tab2.Columns.Add("Image2", typeof(string));
                tab2.Columns.Add("Image3", typeof(string));
                tab2.Rows.Add(1, 2, "yes", "no", "yes");
                tab2.Rows.Add(2, 1, "no", "no", "no");
                tab2.Rows.Add(3, 3, "yes", "yes", "yes");
                var result = from employee in tab1.AsEnumerable()
                              from Doc in tab2.AsEnumerable()
                              where employee.Field<int>("Id") == Doc.Field<int>("EmpId")
                              select new
                              {
                                  Name = employee.Field<string>("Name"),
                                  Documents = (new List<string>() { Doc.Field<string>("Image1"), Doc.Field<string>("Image2"), Doc.Field<string>("Image3") })
                                  .Sum(s => { var count = 0; if (s.Contains("yes")) { count++; } return count; })
                              };
                foreach (var item in result)
                {
                    Console.WriteLine(item.Name + ":" + item.Documents);
                }
            }
    }
    }
    

    Here is my result:

    If you have any other question about my reply, please contact me freely.

    Best Regards,

    Bob



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 25, 2017 3:22 AM