CODEWITHSUNDEEP
sqljoin
AKIWEB
AKIWEB

Reputation: 19612

Get the count from the tables and show the sum of each column

This is below data in Table2

ID2        |    Count2
-----------+-----------
1345653         5
534140349       5
682527813       4
687612723       3
704318001       5

This is my below data in Table1

ID1        |    Count1
-----------+-----------
1345653         4
704318001       4
  1. If you see the above table this 1345653 in Table2 has 5 count but in Table1 it has 4 count, In the same way this ID 704318001 in Table2 has 5 count but in Table1 it has 4 count.
  2. And also one more thing I need to show, I don't know whether it is possible to do it or not. I want to show the sum of each column also in the end, like sum of Count2 column in the end and sum of Count1 column in the end.

So I need to show like this in the Output. I have my working query for the first part, but I am not sure how to work on second part.

ID              Count2   Count1
-----------------------------------
1345653         5        4
534140349       5        0
682527813       4        0
687612723       3        0
704318001       5        4
               22        8

Is this possible in sql? If Yes, how can I achieve that?

I have created the SQL fiddle in which only the count thing is working(means first part), And I am not sure how to add the sum of each column feature in that SQL query.

Upvotes: 1

Views: 109

Answers (3)

jtimperley
jtimperley

Reputation: 2544

You're looking for a grouping "with rollup". I believe the following query should provide what you desire. Assuming you're using SQL Server...

SELECT
    ISNULL(Table1.ID1, Table2.ID2) AS ID,
    SUM(ISNULL(Table2.Count2, 0)) AS Count2,
    SUM(ISNULL(Table1.Count1, 0)) AS Count1
FROM Table1
FULL JOIN Table2 ON Table2.ID2 = Table1.ID1
GROUP BY
    ISNULL(Table1.ID1, Table2.ID2) WITH ROLLUP

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

Will it work for you?

select id2,  SUM(count2),SUM(coalesce(count1, 0)) as count1 
from table2 
 left outer join table1 
  on id1=id2
 group by id2
 with rollup

It's mysql syntax, but most RDMS support ROLLUP with a small difference in syntax

Upvotes: 1

levi
levi

Reputation: 22697

In standard sql:

Select id, Count2,Count1, Count2+Count1 as CountSum
from table2 
left outer join table1 on table1.id=table2.id

Upvotes: 0

Related Questions