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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 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[^]

OriginalGriff · Answer 2 · 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;
        }
    }

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

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0