Count Data from multiple tables
asp.net, grouping, sql, sql-server
Solution
SELECT t2.t2,t3.t3,sum(t3.Quantity)
FROM T2 AS T2
INNER JOIN T3 AS T3 ON t3.t2_serno = t2.t2_serno
GROUP BY t2.t2,t3.t3
In essence, you wanted to group things by 1 primary grouping (t2) and 1 secondary grouping (t3). So when you do a sql server `group by`, it creates grouping within groupings (if you have multiple grouping elements). The leftmost grouping element will be the primary grouping and reading from left to right will be the sub groupings. That's how I got `GROUP By t2.t2,t3.t3`.
Once I understood how you wanted it grouped, I looked at the column quantity. You wanted the sum of the quantity column in t3. Since t3.t3 is a sub group of t2.t2, sql server applied the column aggregate to all rows by the t3.t3 sub group, which is already grouped by the t2.t2 grouping.
TL;DR: The group by clause in sql server has precendent from left to right, creating groupings with in groupings. If you apply an aggregate to a column within a sub group, the aggregate will magically (very logically if you understand the sql engine) work for every item in the sub group and start over at each new sub group instance.
Problem
I have 3 tables T1,T2,T3. Each of them has a relation to another. ``` SELECT T1.T1_Serno, T2.T2_Serno, T3.T3_Serno, T1.T1,COUNT( T3.T3_Serno) as Count_1, T2.T2, T3.T3, T3.Quantity FROM T1 INNER JOIN T2 ON T1.T1_Serno = T2.T1_Serno INNER JOIN T3 ON T2.T2_Serno = T3.T2_Serno GROUP BY T1.T1_Serno, T2.T2_Serno, T3.T3_Serno, T1.T1, T2.T2, T3.T3, T3.Quantity ``` and the result is I am looking to get the Count_1 to show me how many T3 are there and Count_2 to have the relative Count of Quantity of T3. I am expecting a result like ``` T2 T3 Count_1 Count_2 Apple Apple1 3 80 Apple Apple2 3 80 Apple Apple3 3 80 ------- Google Google1 1 40 ------ Nokia Nokia1 1 15 ``` So that when I put this in a grid I can use the Counts as Footer. Something like this but with the totals beneath each Group.