# Need to group and total on 3 conditions

I have a table that looks like this:
----------------------------------------------------
Amount               FB               PLC
5.00                    -                  -
6.00                    -                  -
7.00                    -                  -
8.00                    Y                 -
9.00                    Y                 -
10.00                   -                 Y
11.00                   -                 Y
12.00                   Y                Y
13.00                   Y                Y

I want to total and group on the amount field based on:
----------------------------------------------------
New:         Total of Amount where FB = null and PLC = null
FB:            Total of Amount where FB = Y
PLC:          Total of Amount where PLC = Y
PLCandFB: Total of Amount where FB = Y and PLC = 7

The results should like this:
----------------------------------------------------
New       = 18
FB          = 17
PLC        = 21
PlCandFB = 25
----------------------------------------------------
GrandTotal = 81
###### Who is Participating?
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.

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl, and for the GrandTotal line just add a grouping to whatever report you are going to display this:
``````SELECT a.label, a.amount
FROM (
SELECT 'New' as label, SUM(Amount) as amount, 0 as sort_order
FROM YourTable
WHERE FB IS NULL AND PLC IS NULL
UNION ALL
SELECT 'FB', SUM(Amount), 1
FROM YourTable
WHERE FB = 'Y'
UNION ALL
SELECT 'PLC', SUM(Amount), 2
FROM YourTable
WHERE PLC = 'Y'
UNION ALL
SELECT 'PLCandFG', SUM(Amount), 3
FROM YourTable
WHERE FB='Y' AND PLC = 7) a
ORDER BY a.sort_order
``````
0

Experts Exchange Solution brought to you by

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

Author Commented:
I am getting:

New          18
FB          42
PLC          46
PLCandFG    25

Changed PLC = 7 to PLC = 'Y' on your last condition
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:
It appears that the desired result doesn't match the filtering criteria and mock data in this question for the last three rows.   Otherwise you laid it out real well.
0
Author Commented:
I think need to add to condition is null for the blanks (-) and it works.  Checking..

Amount               FB               PLC
5.00                    -                  -
6.00                    -                  -
7.00                    -                  -
8.00                    Y                 -
9.00                    Y                 -
10.00                   -                 Y
11.00                   -                 Y
12.00                   Y                Y
13.00                   Y                Y
0
Author Commented:
Modified it to add is null condition to the blank columns and it works.  Maybe I should have put null in those columns in my question?  Copy and paste from below into another post and I'll accept.  Thanks!

SELECT a.label, a.amount
FROM (
SELECT 'New' as label, SUM(Amount) as amount, 0 as sort_order
FROM YourTable
WHERE FB IS NULL AND PLC IS NULL
UNION ALL
SELECT 'FB', SUM(Amount), 1
FROM YourTable
WHERE FB = 'Y' and PLC is null
UNION ALL
SELECT 'PLC', SUM(Amount), 2
FROM YourTable
WHERE PLC = 'Y' and FB is null
0
Author Commented:
No need to repost.   I'll accept your post as is.

Correct results are:

New          18
FB          42
PLC          46
PLCandFG    25
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:

btw I learned the sort_order trick from having to deal with a ton of accounting reports, where single SQL statements would populate each row, and I had to get the row order correct.
0
freelancerCommented:
btw; if that Grand Total is required you could use a CTE along these lines:

with CTE (
do the base query here
)
select label, amount
from (
select label, amount, sort_order
from CTE
union
select  'Grand Total', sum(CTE.amount), 99
from CTE
) as x
oder by sort_order
0
Author Commented:
Awesome.  Thanks!
0
###### 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 2005

From novice to tech pro — start learning today.