0

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
2

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
|improve this answer|||||
  • Hmm... Let me try – SovereignSun Nov 29 '16 at 13:29
  • IBExpert says "Nested aggregate functions are not allowed." – SovereignSun Nov 29 '16 at 13:31
  • You're doing something different from the suggested solution. This solution does not have nested aggregate functions. – Serge Nov 29 '16 at 13:34
  • Oh yeh.. Thank you all so much. I forgot to remove Sum(positions.quantity) within the case function. – SovereignSun Nov 29 '16 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.