Count Data from multiple tables

Fhd.ashraf Published at Dev

Fhd.ashraf

I have 3 tables T1,T2,T3. Each of them has a relation to another.

enter image description here

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

enter image description here

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.

enter image description here

gh9

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-7

Comments

0 comments

From Dev

Related Related

Article