CODEWITHSUNDEEP
sqlcountsap-ase
Novice_Techie
Novice_Techie

Reputation: 444

Find a running count of a column name in sybase

My table has a column like enter image description here

No i need to calculate a running count of the column value in other column like enter image description here

I am not able to get it, tried count(column_name) but it's giving me length of a column. Any idea how to do it in Sybase (ASE)??

Upvotes: 1

Views: 1166

Answers (1)

John
John

Reputation: 159

Tim Biegeleisen hinted at the problem - determining a sort order for the devices.

If you don't care about the order of the returned rows, you could use:

SELECT     a.Device_name,
           ( SELECT count(*) FROM Devices 
             WHERE Device_name <= a.Device_name) AS "Running Count"
FROM       Devices a 
ORDER BY   Device_name

This gives:

Device_name          Running Count
-------------------- -------------
Android                          1
Apple                            2
Blackberry                       3
Unix                             4
Windows                          5

If you have a column (eg. "ID") that determines the order of the devices, then you could use code like this:

SELECT     a.Device_name,
           ( SELECT count(*) FROM Devices 
             WHERE ID <= a.ID) AS "Running Count"
FROM       Devices a 
ORDER BY   Device_name

giving us the running count in the original order:

Device_name          Running Count
-------------------- -------------
Apple                            1
Blackberry                       2
Windows                          3
Android                          4
Unix                             5

Of course, the order in which you get the results is not guaranteed unless you use "order by".

Upvotes: 1

Related Questions