DBA > Job Interview Questions > Sybase Interview Questions and Answers

How can I return number of rows that are returne

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How can I return number of rows that are returned from a grouped query without using a temporary table?

This question is certainly not rocket science, but it is often nice to know how many rows are returned as part of a group by. This might be for a report or a web query, where you would want to tell the user how many rows were returned on page one. It is easy using a temp table, but how to do it without a temp table is a little harder. I liked this solution and thought that it might not be obvious to everyone, it was certainly educational to me. Thanks go to Karl Jost for a very nice answer.

 
So, give data like:

name item
---- ----
Brown 1
Smith 2
Brown 5
Jones 7


you wish to return a result set of the form:

name sum(item) rows
---- --------- ----
Brown 6 3
Jones 7 3
Smith 2 3

rather than

name sum(item) rows ---- --------- ---- Brown 6 2 Jones 7 1 Smith 2 1

Use the following, beguilingly simple query:

select name, sum(item), sum(sign(count(*)))
from data
group by name

(Continued on next question...)

Other Job Interview Questions