Number of row counts based on group by clause in SQL server

Question

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 22-Nov-20 20:20pm

Member 14965378

Updated 23-Nov-20 19:56pm

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

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

OriginalGriff · Answer 1 · 2020-11-22T21:01:00

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

Richard Deeming · Answer 2 · 2020-11-23T19:56:00

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[^]

Number of row counts based on group by clause in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Number of row counts based on group by clause in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us