Click here to Skip to main content
14,828,337 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi friends,
I have following two tables in asp.net C# ( or Sql Server)
table1 : Raj
name   place
rrk    hyd
bbr    kkd
rrk    hyd
bbr    kkd
bbr    jju
bbr    iio

table2 : Kri
Name    place
rrk      hyd
rrk      jjj
bbr      kkm
my output would be as the following. The code should be in asp.net C#. Database in Sql Server.
name   count from table1    count from table2    total count
rrk        2                     2                 4
bbr        4                     1                 5


What I have tried:

SELECT d1.name, COUNT(d1.name) as d1_total,COUNT(d2.name) as d2_total, COUNT(d1.name) + COUNT(d2.name) as summ FROM Raj  d1 INNER JOIN Kri d2 ON d1.name = d2.name GROUP BY d1.name;
Posted
Updated 23-Nov-20 19:56pm
v2

Try:
C#
string sql = "SELECT d1.name, " +
             "COUNT(d1.name) AS [Count from Table1], " +
             "COUNT(d2.name) as [Count from Table2], " +
             "COUNT(d1.name) + COUNT(d2.name) as [Total Count] " +
             "FROM Raj d1 " +
             "INNER JOIN Kri d2 " +
             "   ON d1.name = d2.name " +
             "GROUP BY d1.name;";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter(sql, con))
        {
        DataTable dt = new DataTable();
        da.Fill(dt);
        myDataGridView.DataSource = dt;
        }
    }
   
Comments
Richard Deeming 24-Nov-20 1:56am
   
That would return counts of 4, 4, 8 for both rows. :)
Assuming every name appears at least once in both tables, then this should work:
SQL
SELECT 
    d1.name, 
    d1_count,
    d2_count, 
    d1_count + d2_count As total 
FROM 
    (SELECT name, COUNT(1) As d1_count FROM @Raj GROUP BY name) As d1
    INNER JOIN (SELECT name, COUNT(1) As d2_count FROM @Kri GROUP BY name) As d2 
    ON d1.name = d2.name 
;
If any name could appear in only one table, you'll need to use an OUTER JOIN instead:
SQL
SELECT 
    IsNull(d1.name, d2.name) As name,
    IsNull(d1_count, 0) As d1_count,
    IsNull(d2_count, 0) As d2_count,
    IsNull(d1_count, 0) + IsNull(d2_count, 0) As total 
FROM 
    (SELECT name, COUNT(1) As d1_count FROM @Raj GROUP BY name) As d1
    FULL OUTER JOIN (SELECT name, COUNT(1) As d2_count FROM @Kri GROUP BY name) As d2 
    ON d1.name = d2.name 
;
Visual Representation of SQL Joins[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900