T-SQL How to sum multiple columns and get the totals by the column name?

Hi,
I have a table that stores information about activity by store detectives that has a number of boolean fields that on an application form are tick boxes where one or more of them can be ticked per row, I also have an amount field that the user enters an amount in that relates to these ticked fields. eg:

ID  Arrest  Deter Theft  AmountRecovered
1    1           0         0        100.00
2    1           0         1           50.00
3    0           1         0         100.00
4    1            0        0            25.00

What I want to do is get sums for each if the three actions (Arrest, Deter, Theft) so that I can produce a graph, the data needs to be in the form:

ActionType    AmountRecovered
Arrest              175.00
Deter               100.00
Theft                  50.00

I have tried this (using the real field names):

SELECT ArrestYN AS A
             ,DeterYN AS B
            ,TheftYN AS C
          ,SUM(TotalAmount) AS TotalValue
FROM IncidentReport
WHERE (IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate()) AND RecordIsDeletedYN=0
GROUP BY [ArrestYN]
      ,[DeterYN]
      ,[TheftYN]
HAVING SUM(TotalAmount) > 0

This produces results like this:

A      B      C      TotalValue
0      0      0      174.18
0      0      1      1615.81
0      1      0      1218.23
1      0      0      1395.59
1      0      1      79.64
1      1      0      45.01

Obviously the results shown are over a lot more rows than the simplified example at the top.
I appreciate that I am getting more than 3 rows of results as it is grouping on what appears to be the unique combinations of the ticked columns and I need to eliminate that.  Also that this approach will count some of the values twice where the user has ticked more than one of the three items in a single row in the database and this is OK as the end users want to see totals where the given Action is used irrespective of whether that means counting the same row's value in two different Action's totals.

Any help appreciated.

Siv
SivAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
I created a temporary @Table and tested this out. You're gonna like the use of UNPIVOT in this solution:
DECLARE @Table TABLE (
    ID      tinyint     NOT NULL IDENTITY(1, 1),
    Arrest  tinyint     NOT NULL,
    Deter   tinyint     NOT NULL,
    Theft   tinyint     NOT NULL,
    Amount  money       NOT NULL )

INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,0,100)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,1,50)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (0,1,0,100)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,0,25)

SELECT  ActionType,
        SUM(Amount) AS AmountRecovered
FROM    @Table
UNPIVOT (Include FOR ActionType IN (Arrest, Deter, Theft)) AS unpvt
WHERE   Include = 1
GROUP BY ActionType

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
Give this a whirl (assuming there's only three columns we care about) ...

SELECT 'Arrest' as action, SUM (CASE WHEN Arrest = 1 THEN AmountRecovered ELSE 0 END) as AmountRecovered
FROM YourTable
GROUP BY Arrest
UNION ALL
SELECT 'Deter', SUM (CASE WHEN Deter = 1 THEN AmountRecovered ELSE 0 END)
FROM YourTable
GROUP BY Deter
UNION ALL
SELECT 'Theft', SUM (CASE WHEN Theft = 1 THEN AmountRecovered ELSE 0 END)
FROM YourTable
GROUP BY Theft

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
While PIVOT rotates rows into columns, UNPIVOT does the reverse by rotating columns into rows.

Since your columns were predetermined (Arrest, Deter, Theft), that was right up UNPIVOT's alley. Since your query only summed those that were flagged with a 1, the UNPIVOT allowed for merging their values into a field called "Include". Therefore, it was simply a matter of summing where Include = 1.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

SivAuthor Commented:
@dsacker
Brilliant! This appears to do the job.
Siv
SivAuthor Commented:
@Jim Horn
I pinched some of dsacker's code and bent your code to try it so it looks like this:

DECLARE @Table TABLE (
    ID      tinyint     NOT NULL IDENTITY(1, 1),
    Arrest  tinyint     NOT NULL,
    Deter   tinyint     NOT NULL,
    Theft   tinyint     NOT NULL,
    Amount  money       NOT NULL )

INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,0,100)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,1,50)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (0,1,0,100)
INSERT INTO @Table (Arrest, Deter, Theft, Amount) VALUES (1,0,0,25)

/*
SELECT  ActionType,
        SUM(Amount) AS AmountRecovered
FROM    @Table
UNPIVOT (Include FOR ActionType IN (Arrest, Deter, Theft)) AS unpvt
WHERE   Include = 1
GROUP BY ActionType
*/

SELECT 'Arrest' as action, SUM (CASE WHEN Arrest = 1 THEN Amount ELSE 0 END) as AmountRecovered
FROM @Table
GROUP BY Arrest
UNION ALL
SELECT 'Deter', SUM (CASE WHEN Deter = 1 THEN Amount ELSE 0 END)
FROM @Table
GROUP BY Deter
UNION ALL
SELECT 'Theft', SUM (CASE WHEN Theft = 1 THEN Amount ELSE 0 END)
FROM @Table
GROUP BY Theft

And it gives these results:

action      AmountRecovered
Arrest      0.00
Arrest      175.00
Deter      0.00
Deter      100.00
Theft      0.00
Theft      50.00

I think (with greatest respect) I'll go with dsacker's version as it seems slightly more concise and doesn't suffer with the rows containing zeros, but I have retained your method as well in case I can use it in the future on other similar problems.

Thanks very much for your help and quick response.

Siv
SivAuthor Commented:
@dsacker,

Thanks for your very full and easy to follow example, I will use this technique as it seems to do exactly what I was after.

Siv
Jim HornMicrosoft SQL Server Data DudeCommented:
No prob.  Glad you have a working solution.
SivAuthor Commented:
@dsacker
Just one further question on UNPIVOT, This is the actual T-SQL that I got working from your example:

SELECT ActionType,
        SUM(TotalAmount) AS TotalValue
FROM IncidentReport
UNPIVOT (Include For ActionType IN (ArrestYN
      ,DeterYN
      ,TheftYN
      ,ViolentBehaviourYN
      ,AbusiveBehaviourYN
      ,BanFromStoreYN
      ,CriminalDamageYN
      ,UnderagePurchaseYN
      ,CreditCardFraudYN
      ,AntiSocialBehaviourYN
      ,SuspiciousBehaviourYN
      ,OtherYN)) as unpvt
WHERE (IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate()) AND RecordIsDeletedYN=0 AND Include=1
GROUP BY ActionType

This produces this result:
ActionType                          TotalValue
AbusiveBehaviourYN                  44.97
AntiSocialBehaviourYN           29.99
ArrestYN                               1520.24
BanFromStoreYN                        279.77
CreditCardFraudYN                  32.40
CriminalDamageYN                    0.00
DeterYN                                     1263.24
OtherYN                                         19.98
SuspiciousBehaviourYN      125.14
TheftYN                                    1695.45
UnderagePurchaseYN                32.40
ViolentBehaviourYN                93.48

Brilliant.

The trouble is I need to join another table called "Site" so that I can produce the above list for a given Region and Customer . I'd like to include the other table by using an inner join like this:

SELECT ActionType, SUM(TotalAmount) AS TotalValue
FROM IncidentReport inner join Site on IncidentReport.fkSiteID=Site.SiteID
UNPIVOT (Include For ActionType IN (ArrestYN,
            DeterYN,
            TheftYN,
            ViolentBehaviourYN,
            AbusiveBehaviourYN,
            BanFromStoreYN,
            CriminalDamageYN,
            UnderagePurchaseYN,
            CreditCardFraudYN,
            AntiSocialBehaviourYN,
            SuspiciousBehaviourYN,
            OtherYN)) as unpvt
WHERE IncidentReport.fkCustomerID=3
        AND IncidentReport.RecordIsDeletedYN=0
        AND (IncidentReport.IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate())
        AND Include=1
GROUP BY ActionType

This gives me this error when I try and construct it like the above:

Msg 8156, Level 16, State 1, Line 14
The column 'fkCustomerID' was specified multiple times for 'unpvt'.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "IncidentReport.fkCustomerID" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "IncidentReport.RecordIsDeletedYN" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "IncidentReport.IncidentDate" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "IncidentReport.IncidentDate" could not be bound.

Is there any way I can do this?

"Help me Obi-Wan you're my only hope!"
SivAuthor Commented:
One additional thing, in the demo I gave I used the fields A B C D etc as the fields like ArrestYN are known by the security officers as codes A B C etc, I tried doing this in the original T-SQL that worked as shown above like this:

SELECT ActionType,
        SUM(TotalAmount) AS TotalValue
FROM IncidentReport
UNPIVOT (Include For ActionType IN (ArrestYN as A
      ,DeterYN AS B
      ,TheftYN AS C
      ,ViolentBehaviourYN AS D
      ,AbusiveBehaviourYN AS E
      ,BanFromStoreYN AS F
      ,CriminalDamageYN AS G
      ,UnderagePurchaseYN AS H
      ,CreditCardFraudYN AS I
      ,AntiSocialBehaviourYN AS J
      ,SuspiciousBehaviourYN AS K
      ,OtherYN AS L)) as unpvt
WHERE (IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate()) AND RecordIsDeletedYN=0 AND Include=1
GROUP BY ActionType

However when you run that it gives the error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.

Is there any way that you can use aliases with UNPIVOT?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.