CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
frgtv10
frgtv10

Reputation: 5460

SQL Count returns wrong result

My SQL query:

              SELECT
                TO_CHAR(se2.ID) S_ID,
                TO_CHAR(se2.file_name) S_NAME,
                TO_CHAR(pl.pid) P_ID,
                count (*) COUNTER
              FROM
                pl
              join po on po.id = pl.po_id 
              join ppl on po.fk_product_id = ppl.fk_product
              join se on se.id = po.fk_product_id
              join  se2 on se2.id = ppl.fk_parent
              WHERE 
                pl.project_id = 227739470
              GROUP BY se2.ID, se2.file_name, pl.pid

Returns me 16 rows, but count(*) is 1 in every result row! I want to get the amount of all rows in every result line. Whats the problem here?

Upvotes: 1

Views: 1050

Answers (1)

David Aldridge
David Aldridge

Reputation: 52386

If you want every row to have a column with the total number of rows in it, then use:

count(*) over () total_rows

You do not need to use a group by as this is an analytic function, not an aggregate function.

Upvotes: 7

Related Questions