SQL: number of rows with specific value's (dynamic)

Question

Hard to explain problem here :)

I need to get a new column I have the amount of lines F.E. "RIT-17000263-T: 1" would have an extra column "Total Sequences": '9' "RIT-17000264-T: 1" would be "Total Sequences": '2'

Any help is welcome!:

SELECT DISTINCT
    CONCAT(R.[Trip No_],'-', CONCAT('T: ', LEFT(R.[Partial trip Line No_],1))) AS 'TRIP',
    R.[File] AS 'FILE',
    R.[Sequence No_] AS 'SEQUENCE IN PTRIP'
FROM            
    [Route] AS R
WHERE           
    (R.[Trip No_] = 'RIT-17000263' 
     OR R.[Trip No_] = 'RIT-17000264')
GROUP BY 
    CONCAT(R.[Trip No_], '-', CONCAT('T: ', LEFT(R.[Partial trip Line No_],1))), 
    R.[File], R.[Sequence No_]

Screenshot

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jan 25, 2017 at 16:28

S3S · Accepted Answer · 2017-01-25 17:35:39Z

2

Perhaps wrap it in a CTE...

with cte as(
    SELECT DISTINCT
    CONCAT(R.[Trip No_],'-', CONCAT('T: ', LEFT(R.[Partial trip Line No_],1))) AS 'TRIP',
    R.[File] AS 'FILE',
    R.[Sequence No_] AS 'SEQUENCE IN TRIP'
FROM            
    [Route] AS R
WHERE           
    (R.[Trip No_] = 'RIT-17000263' 
     OR R.[Trip No_] = 'RIT-17000264')
GROUP BY 
    CONCAT(R.[Trip No_], '-', CONCAT('T: ', LEFT(R.[Partial trip Line No_],1))), 
    R.[File], R.[Sequence No_])

select
    c.TRIP,
    c.[FILE],
    c.[SEQUENCE IN TRIP],
    c2.CT as [Total Sequences]
from 
    cte c
    join (select TRIP, count(TRIP) as CT from cte group by TRIP) c2 on c2.TRIP = c.TRIP

edited Jan 25, 2017 at 17:35

answered Jan 25, 2017 at 16:48

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gregory Over a year ago

Approach looks great!, but i'm getting an error on the join? An expression of non-boolean type specified in a context where a condition is expected, near 'c2'

S3S Over a year ago

Sorry about that, fixed it @Gregory

S3S Over a year ago

Cheers Mate! @Gregory

Collectives™ on Stack Overflow

SQL: number of rows with specific value's (dynamic)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related