Search code examples
sqlcountsap-ase

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)??

Solution

  • 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".