x

Count Data from multiple tables

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

T1_Serno   T1
--------------
1          T1

Table 2

  T2_Serno  T1_Serno  T2
    1           1     apple
    2           1     google
    3           1     Nokia

Table 3
    T3_Serno  T2_Serno   T3       Quantity
     1          1       apple1    20
     2          1       apple2    15
     3          2       Gogole1   10
     4          3       Nokia1    5

I have tried a few scripts .:

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

I want something like this

   T2    T3      Count_1 Count_2
    Apple Apple1    3    35
    Apple Apple2    3    35
      -------
    Google Google1  1    10
    ------
    Nokia Nokia1    1    5
more ▼

asked Apr 22 at 10:47 AM in Default

fashraf gravatar image

fashraf
418 13 14 20

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Try this. You can use aggregate windowing functions to get the results you want.

declare @T1 table (T1_Serno int, T1 varchar(50))
declare @T2 table (T2_Serno int, T1_serno int, T2 varchar(50))
declare @T3 table (T3_Serno int, T2_serno int, T3 varchar(50), Quantity int)

insert into @T1 select 1, 'T1'
insert into @T2 select 1, 1, 'apple'
insert into @T2 select 2, 1, 'google'
insert into @T2 select 3, 1, 'nokia'
insert into @T3 select 1, 1, 'apple1',20
insert into @T3 select 2, 1, 'apple1',15
insert into @T3 select 3, 2, 'google1',10
insert into @T3 select 4, 3, 'nokia1',5

select
    T2.T2,
    T3.T3,
    count(*)over(partition by T3.T3),
    sum(quantity)over(partition by T3.T3)
from @T1 T1
join @T2 T2 on T2.T1_serno = T1.T1_Serno
join @T3 T3 on T3.T2_serno = T2.T2_Serno
more ▼

answered Apr 22 at 11:10 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Sir, It works perfect if i run the entire code..But how do I use it as a query to run on my Gridview or repeater.? Please suggest. Thank you.
Apr 22 at 03:21 PM fashraf
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x69
x37
x17

asked: Apr 22 at 10:47 AM

Seen: 257 times

Last Updated: Apr 22 at 03:21 PM