Get totals from related table

[Zadj]

Hi,

I'm trying get some summary results from two related tables. I'm looking to find the most efficient way to return this data in one result set.

The tables can be summed up as follows:

MainTable
MainID INT,
Description VARCHAR(50)
Eg:
MainID   Description
1            John
2            Adam
3            Fred

ChildTable
ChildID  INT,
ParentID  INT,
IsImportant BIT,
HasBeenUsed BIT
Eg:
ChildID   ParentID   IsImportant   HasBeenUsed
101         1             1               0
102         1             1               1
103         2             0               0
104         2             1               0


The results that I want are as follows:
MainID Description TotalChild AllImportant AllUsed
1          John          2          2          1
2          Adam          2          1          0
3          Fred          0          0          0

I have tried using common table expressions and that appears to work fine if all I want is the AllImportant but when I try to add the AllUsed it goes wrong.

Can anyone put me on the right path as I feel I'm getting a bit lost on this one.

Thanks,
Zadjil

[serkan sendur]

can you clearify what you ask a little bit please?

[nav33n]

umm.. He wants to count the number of child entries for a particular parent. For example, for mainid=1(description John), there are 2 entries in child. He wants to calculate the entries of each parent, put it in the first column named Total child. Then the sum of Is_important in the second column and the sum of has_been_used in the 3rd column. He wants this to be returned as one resultset !

Edit: Zadj, If you could show us what/how you have achieved this, we can try to debug/find errors in it.

[Zadj]

Thank you serkansendur and nav33n for your replies.

After a good nights sleep and a little inspiration in the shower :icon_smile: I have worked out how to do this. I had managed to make the issue more complicated than it needed to be.

This is my solution

SELECT 
   MainID, 
   Description, 
   COUNT (ChildTable.ChildID) AS [TotalChild],
   SUM (CAST(ChildTable.IsImportant AS INT)) AS [AllImportant],
   SUM (CAST(ChildTable.HasBeenUsed AS INT)) AS [AllUsed]
FROM 
   MainTable LEFT JOIN
   ChildTable ON ChildTable.ParentID = MainTable.MainID
GROUP BY 
   MainTable.MainID, MainTable.Description
ORDER BY 
   MainTable.MainID

Here is my testing code

Create TABLE #MainTable (
MainID INT,
Description VARCHAR(50)
)

INSERT INTO #MainTable(MainID,Description)Values(1,'John')
INSERT INTO #MainTable(MainID,Description)Values(2,'Adam')
INSERT INTO #MainTable(MainID,Description)Values(3,'Fred')

Create table #ChildTable(
ChildID  INT,
ParentID  INT,
IsImportant BIT,
HasBeenUsed BIT
)
INSERT INTO #ChildTable(ChildID  , ParentID  , IsImportant  , HasBeenUsed)Values (101,1,1,0)
INSERT INTO #ChildTable(ChildID  , ParentID  , IsImportant  , HasBeenUsed)Values (102,1,1,1)
INSERT INTO #ChildTable(ChildID  , ParentID  , IsImportant  , HasBeenUsed)Values (103,2,0,0)
INSERT INTO #ChildTable(ChildID  , ParentID  , IsImportant  , HasBeenUsed)Values (104,2,1,0)

SELECT 
	MainID, Description, 
	COUNT (#ChildTable.ChildID) AS [TotalChild],
	SUM (CAST(#ChildTable.IsImportant AS INT)) AS [AllImportant],
	SUM (CAST(#ChildTable.HasBeenUsed AS INT)) AS [AllUsed]
FROM 
	#MainTable LEFT JOIN
	#ChildTable ON #ChildTable.ParentID = #MainTable.MainID
GROUP BY 
	#MainTable.MainID, #MainTable.Description
ORDER BY 
	#MainTable.MainID


DROP TABLE #MainTable
DROP TABLE #ChildTable

Thanks again.
Zadjil

[nav33n]

Woah! Thats a lil complicated query !