Interbase SQL Grouping and displaying I have a database with different data I need to make a query to select a group with summed quantities.

Two tables: Positions and Documents.

There can be many positions in one document both are connected by unique_id.

The Documents have two different statuses: In and Out

I need to see the result of these two status on one row with a column for each case, but instead my query gives me two rows. How do I do it?

My query doesn't give me this result:

Select   documents.code
        ,positions.fullname
        ,case when documents.status='In' then Sum(positions.quantity) else 0 end as DocIn
        ,case when documents.status='Out' then Sum(positions.quantity) else 0 end as DocOut
from positions
    inner join documents
        on documents.unique_id=positions.unique_id
group by documents.code, positions.fullname, documents.status
You are getting two rows per document because you are grouping by documents.status. You need to use SUM() slightly differently and then remove documents.status from the GROUP BY clause:

Select   documents.code
        ,positions.fullname
        ,SUM(case when documents.status='In' THEN positions.quantity ELSE 0 END) AS DocIn
        ,SUM(case when documents.status='Out' THEN positions.quantity ELSE 0 END) AS DocOut
from positions 
inner join documents
 on documents.unique_id=positions.unique_id 
 group by documents.code, positions.fullname