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
Delta7428Asked:
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.

Jim HornMicrosoft 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

Open in new window

0

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
Delta7428Author Commented:
I am getting:

New          18
FB          42
PLC          46
PLCandFG    25

Changed PLC = 7 to PLC = 'Y' on your last condition
0
Jim HornMicrosoft 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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Delta7428Author 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
Delta7428Author 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
Delta7428Author Commented:
No need to repost.   I'll accept your post as is.  

Correct results are:

New          18
FB          42
PLC          46
PLCandFG    25
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim

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
PortletPaulfreelancerCommented:
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
Delta7428Author 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.