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, 2014 at 10:47 AM in Default

avatar image

fashraf
538 16 21 28

(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, 2014 at 11:10 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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, 2014 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.

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:

x87
x64
x36

asked: Apr 22, 2014 at 10:47 AM

Seen: 532 times

Last Updated: Apr 22, 2014 at 03:21 PM

Copyright 2016 Redgate Software. Privacy Policy