CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
user3190075
user3190075

Reputation: 79

Count and Sum in the same query

I have my query:

SELECT CASE WHEN dt.value = 380 AND p.FlowStatusID <> 5 THEN 'FATTURE C&S'  
            WHEN dt.value = 381 AND p.FlowStatusID <> 5 THEN 'NOTE CREDITO C&S' 
            WHEN dt.value = 380 OR dt.value = 381 AND p.FlowStatusID = 5 THEN 'LATE ARCHIVE C&S' 
            END AS [Tipo Documento]
            ,SUM(p.SystemField5) AS 'Nr Pagine', Count(p.SystemField3) AS 'Nr Documenti' 

FROM dbo.PSDOC p INNER JOIN dbo.listDocumentTypes dt ON p.DocumentTypeId = dt.value

GROUP BY dt.value, p.FlowStatusID

I want to get the sum and the count group by document type

 document type      |   sum         |   count
--------------------------------------------------
FATTURE C&S         |               |
NOTE CREDITO C&S    |               |
LATE ARCHIVE C&S    |               |

but the query result

document type        sum         count
----------------------------------------------
LATE ARCHIVE C&S    |   8      |   5
FATTURE C&S         |   2412   |   1265
FATTURE C&S         |   104254 |   67990
NOTE CREDITO C&S    |   362    |   236
FATTURE C&S         |   167    |   98
LATE ARCHIVE C&S    |   4      |    2
LATE ARCHIVE C&S    |   297107 |   163797

How can I get it like distinct in document type column ?

Upvotes: 0

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SImply remove dt.value and FlowStatusId from the group by and replace it with the full case statement:

SELECT CASE WHEN dt.value = 380 AND p.FlowStatusID <> 5 THEN 'FATTURE C&S'  
            WHEN dt.value = 381 AND p.FlowStatusID <> 5 THEN 'NOTE CREDITO C&S' 
            WHEN dt.value = 380 OR dt.value = 381 AND p.FlowStatusID = 5 THEN 'LATE ARCHIVE C&S' 
            END AS [Tipo Documento],
       SUM(p.SystemField5) AS "Nr Pagine", Count(p.SystemField3) AS "Nr Documenti"
FROM dbo.PSDOC p INNER JOIN
     dbo.listDocumentTypes dt
     ON p.DocumentTypeId = dt.value
GROUP BY CASE WHEN dt.value = 380 AND p.FlowStatusID <> 5 THEN 'FATTURE C&S'  
              WHEN dt.value = 381 AND p.FlowStatusID <> 5 THEN 'NOTE CREDITO C&S' 
              WHEN dt.value = 380 OR dt.value = 381 AND p.FlowStatusID = 5 THEN 'LATE ARCHIVE C&S' 
        END

Upvotes: 2

Related Questions