How to merge two aggregate functions in SQL Server

Question

I need to merge two aggregate functions in SQL Server.

Code:

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY],
    (count(Sno)+1) AS count 
FROM tbl_demographic_reg

Output:

DDMMYY   count

060114  1

I need the above output to be 06011400001 - how to get it? Thanks

Concat & Convert technet.microsoft.com/en-us/library/hh231515.aspx — PeterRing
– PeterRing, Commented Jan 6, 2014 at 9:13

Bridge · Accepted Answer · 2014-01-06 09:32:24Z

2

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') +
    RIGHT('0000'+ CONVERT(VARCHAR,count(Sno)),6)
FROM tbl_demographic_reg

edited Jan 6, 2014 at 9:32

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

answered Jan 6, 2014 at 9:29

Sushil

1961 silver badge8 bronze badges

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

Comments

Szymon · Accepted Answer · 2014-01-06 09:27:36Z

1

You can just concatenate them together, adding necessary zeroes in between:

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') + 
    right('0000' + cast((count(Sno)+1) as varchar(5)), 5)
FROM tbl_demographic_reg

edited Jan 6, 2014 at 9:27

answered Jan 6, 2014 at 9:19

Szymon

43k16 gold badges99 silver badges115 bronze badges

4 Comments

Happy Over a year ago

its showing error "Incorrect syntax near 'cast', expected 'AS'." what to do...?

Szymon Over a year ago

Sorry, misplaced brackets. Please try now.

Happy Over a year ago

thanks...if sno return 10 then o/p is 000010 but i need it 00010..dynamiclly it should reduce zero's how to do...?

Szymon Over a year ago

Is it? When you do SELECT right('0000' + cast(10 as varchar(5)), 5) it returns 00010 correctly...

Collectives™ on Stack Overflow

How to merge two aggregate functions in SQL Server

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related