CODEWITHSUNDEEP
sqlsybase
Muirik
Muirik

Reputation: 6289

SELECT statement to Return a Count for Each Row in Sybase Table

While working on a MongoDB/Node back-end environment I am needing to run some ETLs to pull in data from a previous Sybase environment. I am far less familiar with SQL than I am with Mongo/Node.

The following SELECT statement finds the count for a particular record (row of data) in the bl_serv_staffmember_xref table: 

SELECT Count()
   FROM  "bl_serv_staffmember_xref"
   WHERE ( "bl_serv_staffmember_xref"."staff_member_id" = 129 ) AND 
         ( "bl_serve_staffmember_xref"."isactive" = 1 );

What I need to do is generate a SELECT statement that will return data with that count for EACH row in the table. How would I go about doing that?

I tried this:

SELECT Count()
    FROM  "bl_serv_staffmember_xref"
   WHERE   ( "bl_serv_staffmember_xref"."staff_member_id" is NOT NULL ) AND 
         ( "bl_serv_staffmember_xref"."isactive" = 1 )   ;

But this just returns one total count:

Count()
1   2,452

rather than a count for each staff_member_id, which is what I need.

This is my desired result:

staff_member_id     assignment_count
1                   23
2                   12
3                   16

Upvotes: 1

Views: 372

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use count(*) and group by eg:

SELECT bl_serv_staffmember_xref.staff_member_id, Count(*)
FROM  "bl_serv_staffmember_xref"
WHERE ( "bl_serve_staffmember_xref"."isactive" = 1 )
GROUP BY bl_serv_staffmember_xref.staff_member_id

Upvotes: 2

Related Questions