How to Total 2 Columns Using Pivot

SQL Server Administration
1

Hi experts,

This is my SQL statement with Pivot:

SELECT *
FROM
(
SELECT FirstName, LastName, Client, FreightBillRecdDate, AuditType, CountOfBills
FROM MyTable]
) src
PIVOT
(
SUM(CountOfBills)
FOR AuditType IN (DOM, INTL)
) piv

FirstName LastName Client FreightBillRecdDate DOM INTL

A Long Some Co 2022-12-22 3 NULL

It does result in everything (Dom and Intl) being on the same line,
but if DOM or INTL columns are null, I want to show 0 (zero)

  1. How can I tweak this SUM(CountOfBills) to return 0 if there is no value?

  2. Also, how can I add a TotalBillsProcessed to reflect DOM + INTL?

Thanks.

2 
SELECT
     ISNULL(piv.DOM,0) AS Dom,
     ISNULL(piv.INTL,0) AS INTL,
     ISNULL(piv.DOM,0) + ISNULL(piv.INTL,0) AS TotalBillsProcessed
 ....
1 Like
3

Thanks, @RogierPronk. Sorry for being obtuse but I can't see where to plug that into my block of code:
SELECT *
FROM
(
SELECT FirstName, LastName, Client, FreightBillRecdDate, AuditType, CountOfBills
FROM MyTable]
) src
PIVOT
(
SUM(CountOfBills)
FOR AuditType IN (DOM, INTL)
) piv

4 
SELECT
     ISNULL(piv.DOM,0) AS Dom,
     ISNULL(piv.INTL,0) AS INTL,
     ISNULL(piv.DOM,0) + ISNULL(piv.INTL,0) AS TotalBillsProcessed
FROM
(
SELECT FirstName, LastName, Client, FreightBillRecdDate, AuditType, CountOfBills
FROM MyTable]
) src
PIVOT
(
SUM(CountOfBills)
FOR AuditType IN (DOM, INTL)
) piv
5

Thanks, @RogierPronk That eliminates the null s and it gives a Total but lost the names and client columns.
image

6

You can simply add them to your query, just add piv.* and you will see all the columns. I'm really confused that you can write a pivot but adding a column is to hard. Asking is easy but you should develop your skills more.

SELECT
ISNULL(piv.DOM,0) AS Dom,
ISNULL(piv.INTL,0) AS INTL,
ISNULL(piv.DOM,0) + ISNULL(piv.INTL,0) AS TotalBillsProcessed,
piv.Firstname,
etc, etc...
FROM
(
SELECT FirstName, LastName, Client, FreightBillRecdDate, AuditType, CountOfBills
FROM MyTable]
) src
PIVOT
(
SUM(CountOfBills)
FOR AuditType IN (DOM, INTL)
) piv

2 Likes
7

Thank you very much, @RogierPronk