locked
How to count of each section of the records RRS feed

  • Question

  • Hello ASll,

    I am working on a dataset that should get the count of certain section of the data. I have 3 tables called pipe, lineregulatorytype and line.

    The columns in the Pipe table are podsid, Regulatorytype, classification

    The columns in Lineregulatorytype are code, name

    The name in line regulatory type and the regulatory type column in pipe table are same. the data in pipe table is as

    645           Natural gas        natural gas

    178           natural gas        natural gas

    166           liquids                liquids

    1766         liquids                liquids

    184          liquids                 liquids

    the data in lineregulatorytype is as

    192tran                natural gas

    165liq                  liquids

    Now I want the list with count of pipes for each regulatory type.I want in the result set as

    Code           Regulatory type       classification            count

    192tran        natural gas             natural gas             2

    165liq          liquids                      liquids                     3

    Please help me how to achieve this. Thanks

    Tuesday, November 27, 2012 9:38 PM

Answers

  • Try

    select LT.[Code] as Code, R.[RegulatoryType],R.[Classification], count(*) as [Count]
    
    from Pipe R INNER JOIN LineRegulatoryType LT On R.[RegulatoryType] = LT.[Name]
    
    GROUP BY LT.[Code], R.[RegulatoryType], R.[Classification]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by dgjohnson Tuesday, November 27, 2012 9:46 PM
    Tuesday, November 27, 2012 9:43 PM
  • Try this:

    Declare @tvPipe Table (
    	PodsId			int
    	,RegulatoryType	varchar(20)
    	,Classification	varchar(20)
    )
    Declare @tvLineRegulatoryType Table (
    	Code	varchar(20)
    	,Name	varchar(20)
    )
    
    Insert	@tvPipe
    Select	645, 'Natural Gas', 'Natural Gas'
    Union All
    Select	178, 'Natural Gas', 'Natural Gas'
    Union All
    Select	166, 'Liquids', 'Liquids'
    Union All
    Select	1766, 'Liquids', 'Liquids'
    Union All
    Select	184, 'Liquids', 'Liquids'
    
    Insert	@tvLineRegulatoryType
    Select	'192tran', 'Natural Gas'
    Union All
    Select	'165liq', 'Liquids'
    
    Select	r.Code
    		,p.RegulatoryType
    		,p.Classification
    		,Count(p.RegulatoryType) Cnt
    From	@tvLineRegulatoryType r
    join	@tvPipe p
    	on	r.Name = p.Classification
    Group By r.Code, p.RegulatoryType, p.Classification

    But your structure seems less than ideal.  Is there no ID to join the tables, or is it only name available?

    • Proposed as answer by NandhuJana Wednesday, November 28, 2012 6:50 AM
    • Marked as answer by srisql Thursday, November 29, 2012 3:42 PM
    Tuesday, November 27, 2012 9:45 PM
  • Try the below code,It gives three resultsets First two is your table and Third one Is your resultset output.

    Declare @Pipetable as table(podsid int, Regulatorytype nvarchar(20), classification nvarchar(20))
    Insert into @Pipetable values(645,'Natural gas','natural gas')
    ,(178,'Natural gas','natural gas')
    ,(166,'liquids','liquids')
    ,(1766,'liquids','liquids')
    ,(184,'liquids','liquids') 
    Declare @Lineregulatorytype as table(code nvarchar(20), name nvarchar(20))
    Insert into @Lineregulatorytype values('192tran','natural gas'),('165liq','liquids')
    SELECT * from @Pipetable
    Select * from @Lineregulatorytype
    ---------------------------------------------------
    ;WITH CTE as
    ( 
    Select Regulatorytype,classification,COUNT(*) as Counts from @Pipetable Group by Regulatorytype,classification
    )
    select r.[Code] as Code, LT.[RegulatoryType],LT.[Classification], LT.Counts
    from @Lineregulatorytype R INNER JOIN CTE LT On LT.[RegulatoryType] = R.[Name]
    --------------------------------------------
    ----------------------------------------
    ------------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by NandhuJana Wednesday, November 28, 2012 6:51 AM
    • Marked as answer by srisql Thursday, November 29, 2012 3:42 PM
    Wednesday, November 28, 2012 5:15 AM

All replies

  • Try

    select LT.[Code] as Code, R.[RegulatoryType],R.[Classification], count(*) as [Count]
    
    from Pipe R INNER JOIN LineRegulatoryType LT On R.[RegulatoryType] = LT.[Name]
    
    GROUP BY LT.[Code], R.[RegulatoryType], R.[Classification]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by dgjohnson Tuesday, November 27, 2012 9:46 PM
    Tuesday, November 27, 2012 9:43 PM
  • Try this:

    Declare @tvPipe Table (
    	PodsId			int
    	,RegulatoryType	varchar(20)
    	,Classification	varchar(20)
    )
    Declare @tvLineRegulatoryType Table (
    	Code	varchar(20)
    	,Name	varchar(20)
    )
    
    Insert	@tvPipe
    Select	645, 'Natural Gas', 'Natural Gas'
    Union All
    Select	178, 'Natural Gas', 'Natural Gas'
    Union All
    Select	166, 'Liquids', 'Liquids'
    Union All
    Select	1766, 'Liquids', 'Liquids'
    Union All
    Select	184, 'Liquids', 'Liquids'
    
    Insert	@tvLineRegulatoryType
    Select	'192tran', 'Natural Gas'
    Union All
    Select	'165liq', 'Liquids'
    
    Select	r.Code
    		,p.RegulatoryType
    		,p.Classification
    		,Count(p.RegulatoryType) Cnt
    From	@tvLineRegulatoryType r
    join	@tvPipe p
    	on	r.Name = p.Classification
    Group By r.Code, p.RegulatoryType, p.Classification

    But your structure seems less than ideal.  Is there no ID to join the tables, or is it only name available?

    • Proposed as answer by NandhuJana Wednesday, November 28, 2012 6:50 AM
    • Marked as answer by srisql Thursday, November 29, 2012 3:42 PM
    Tuesday, November 27, 2012 9:45 PM
  • Try the below code,It gives three resultsets First two is your table and Third one Is your resultset output.

    Declare @Pipetable as table(podsid int, Regulatorytype nvarchar(20), classification nvarchar(20))
    Insert into @Pipetable values(645,'Natural gas','natural gas')
    ,(178,'Natural gas','natural gas')
    ,(166,'liquids','liquids')
    ,(1766,'liquids','liquids')
    ,(184,'liquids','liquids') 
    Declare @Lineregulatorytype as table(code nvarchar(20), name nvarchar(20))
    Insert into @Lineregulatorytype values('192tran','natural gas'),('165liq','liquids')
    SELECT * from @Pipetable
    Select * from @Lineregulatorytype
    ---------------------------------------------------
    ;WITH CTE as
    ( 
    Select Regulatorytype,classification,COUNT(*) as Counts from @Pipetable Group by Regulatorytype,classification
    )
    select r.[Code] as Code, LT.[RegulatoryType],LT.[Classification], LT.Counts
    from @Lineregulatorytype R INNER JOIN CTE LT On LT.[RegulatoryType] = R.[Name]
    --------------------------------------------
    ----------------------------------------
    ------------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by NandhuJana Wednesday, November 28, 2012 6:51 AM
    • Marked as answer by srisql Thursday, November 29, 2012 3:42 PM
    Wednesday, November 28, 2012 5:15 AM
  • Thanks so much for your valuable answers.
    Thursday, November 29, 2012 3:43 PM