SQL queries for Concatenate and SUM

Hello All,

I am trying to create an SQL view that will display 2 sets of information:
- 1 extra column which the values will be the Concatenation of the values of 2 columns.
- Once I display this extra concatenate column I would like to get the SUM of values of the "Balance" column "IF" a criteria is met. The criteria is that the Concatenate value should be 551 and 572 AND the Period ID value is 1

Please refer to the example attached.

If you have any other recommendations on how to do this better, please let me knowDatabase.xlsx


Thanks!
Database.PNG
LuiLui77Asked:
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.

Mike EghtebasDatabase and Application DeveloperCommented:
What will your final output would look like with sum column. I want to know how you want the rows to be collapsed. What will be Group By columns.

try:
Select Period, [Period ID], ConcatField, Sum(Balance) As SumVal
From (Period, [Period ID], Balance, Amount + [Account sufix] As ConcatField
From Table1) As D (Period, [Period ID], Balance, ConcatField)
Where ConcatField in (551, 572)
Group By Period, [Period ID], ConcatField

Open in new window

0
LuiLui77Author Commented:
Hi eghtebas! actually I am not sure, The purpose of this is to display the SUM of the 551, 572 account where Period ID = 1 somewhere

I am not sure how and where to display this SUM, could it be possible to create another sql view that grabs the values from this view? just thinking
0
Mike EghtebasDatabase and Application DeveloperCommented:
This will give you:
Select [Period ID], ConcatField, Sum(Balance) As SumVal
From (Period, [Period ID], Balance, Amount + [Account sufix] As ConcatField
From Table1) As D (Period, [Period ID], Balance, ConcatField)
Where ConcatField in (551, 572)
Group By [Period ID], ConcatField

Open in new window


[Period ID]         ConcatField    SumVal
1                               551              3456 not exact
2                               572              4445
3                               551        etc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
This result:
| Period | Period ID | ConcatField | Balance |
|--------|-----------|-------------|---------|
|   2015 |         1 |         551 |      20 |
|   2015 |         1 |         572 |      60 |

Open in new window

Produced by this query:
SELECT
      Period
    , [Period ID]
    , ConcatField
    , SUM(Balance) AS Balance
FROM (
      SELECT
            Period
          , [Period ID]
          , Balance
          , Account + [Account suffix] AS ConcatField
      FROM Table1
      WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')
) AS D
WHERE ConcatField IN ('551', '572')
GROUP BY Period
       , [Period ID]
       , ConcatField

Open in new window

Using this data:
CREATE TABLE Table1
    ([Period] int, [Period ID] int, [Balance] int, [Account] varchar(12), [Account suffix] varchar(12))
;
    
INSERT INTO Table1
    ([Period], [Period ID], [Balance], [Account], [Account suffix])
VALUES
    (2015, 1, 20, '55', '1'),
    (2015, 2, 30, '55', '1'),
    (2015, 3, 40, '55', '1'),
    (2015, 4, 20, '55', '1'),
    (2015, 0, 10, '56', '2'),
    (2015, 1, 20, '56', '2'),
    (2015, 2, 10, '56', '2'),
    (2015, 3, 30, '56', '2'),
    (2015, 4, 40, '56', '2'),
    (2015, 0, 50, '57', '2'),
    (2015, 1, 60, '57', '2'),
    (2015, 4, 80, '57', '2')
;

Open in new window

see: http://sqlfiddle.com/#!3/d745a/3

ps: It is really the asker's responsibility to tell use what the final layout should be.
0
LuiLui77Author Commented:
Ok thank you Paul and eghtebas! getting there with your help.

Now, how would I be able to SUM the overall balances of these accounts all together?
One Total which is the result of everything.
0
Mike EghtebasDatabase and Application DeveloperCommented:
This is one way of doing it (replace #Table1 with your table name)
SELECT
      Period
    , [Period ID]
    , ConcatField
    , SUM(Balance) AS Balance
	, (Select Sum(t.Balance) From #Table1 t
	   WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')) As GrandTotal
FROM (
      SELECT
            Period
          , [Period ID]
          , Balance
          , Account + [Account suffix] AS ConcatField
      FROM #Table1
      WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')
) AS D 
WHERE ConcatField IN ('551', '572')
GROUP BY Period
      , [Period ID]
      , ConcatField

Open in new window


I am sure there are some other ways to do this.

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
But I think you might like this better. Note that the row with all null but sum of all giving what you should be looking at to see some of all:
SELECT
      Period
    , [Period ID]
    , ConcatField
    , SUM(Balance) AS Balance
FROM (
      SELECT
            Period
          , [Period ID]
          , Balance
          , Account + [Account suffix] AS ConcatField
      FROM #Table1
      WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')
) AS D 
WHERE ConcatField IN ('551', '572')
GROUP BY GROUPING SETS((Period, [Period ID], ConcatField), ());

Open in new window

0
PortletPaulfreelancerCommented:
You give us no indication in how you want the additional information presented (so we have to guess what it is you want).

Here is another way of getting the overall total, it s repeated on each row which is helpful if you wished to calculate percentage per row for example.

It looks like this:
| Period | Period ID | ConcatField | Balance | TotalBal |
|--------|-----------|-------------|---------|----------|
|   2015 |         1 |         551 |      20 |       80 |
|   2015 |         1 |         572 |      60 |       80 |

Open in new window

The query:
SELECT
      Period
    , [Period ID]
    , ConcatField
    , SUM(Balance) AS Balance
    , MAX(TotalBal) as TotalBal
FROM (
      SELECT
            Period
          , [Period ID]
          , Balance
          , CA.ConcatField
          , SUM(Balance) OVER() as TotalBal
      FROM Table1
      CROSS APPLY (
           SELECT Account + [Account suffix]
           ) as CA (ConcatField)
      WHERE [Period ID] = 1
      AND ConcatField IN ('551', '572')
) AS D
GROUP BY Period
       , [Period ID]
       , ConcatField

Open in new window


{+edit}
Note the is only one WHERE clause used now, and a CROSS APPLY is used to aid in that. This is so that the overall total is accurate.
http://sqlfiddle.com/#!3/d745a/7
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
Mike EghtebasDatabase and Application DeveloperCommented:
If this is a report for lay user and something like below is desirable:

ThePeriod  Period_ID   ConcatField     Balance  
 Total          for all                  is -->                80                  <-- total row on the top
2015             1                        551                  20
2015             1                        572                  60

Use:
Select ' Total' As ThePeriod
     , ' for all' As Period_ID
	 , '  is -->' As ConcatField
	 , (Select Sum(t.Balance) From #Table1 t
	   WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')) As Balance
From #Table1
Union
SELECT
      cast(Period as varchar(10))
    , cast([Period ID] as varchar(10)) --as Period_ID
    , ConcatField
    , SUM(Balance) AS Balance
FROM (
      SELECT
            Period
          , [Period ID] 
          , Balance  
          , Account + [Account suffix] AS ConcatField
      FROM #Table1
      WHERE [Period ID] = 1
      AND Account IN ('55', '57')
      AND [Account suffix] IN ('1','2')
) AS D 
WHERE ConcatField IN ('551', '572')
GROUP BY Period, [Period ID], ConcatField;

Open in new window


This is not very professional but if it works then use it.

BTW, my previous solution output is:
Period  Period ID   ConcatField     Balance  
2015       1                551                    20
2015       1                572                    60
NULL      NULL        NULL                  80
0
LuiLui77Author Commented:
I was able to accomplish what I needed with both of your queries, 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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.