LuiLui77
asked on
SQL query modification
Hello All,
Recently I posted a question about how to create a SQL query that will concatenate and will SUM some values of some columns of those concatenated field records. That question has been resolved but now I need to add something else to that resultant SQL query.
Below is the URL for the original post:
https://www.experts-exchange.com/questions/28697487/SQL-queries-for-Concatenate-and-SUM.html
The resultant query was the following:
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
Which generated the following table:
Period | Period ID | ConcatField | Balance | TotalBal |
|--------|----------------
| 2015 | 1 | 551 | 20 | 80 |
| 2015 | 1 | 572 | 60 | 80 |
The whole reason for the query was just to get the calculation of the "TotalBal" column, but as you can see the value is repeated along all records.
Is it possible to modify the query to only select one of these records? for instance only the record WHERE ConcatField = 551
Recently I posted a question about how to create a SQL query that will concatenate and will SUM some values of some columns of those concatenated field records. That question has been resolved but now I need to add something else to that resultant SQL query.
Below is the URL for the original post:
https://www.experts-exchange.com/questions/28697487/SQL-queries-for-Concatenate-and-SUM.html
The resultant query was the following:
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
Which generated the following table:
Period | Period ID | ConcatField | Balance | TotalBal |
|--------|----------------
| 2015 | 1 | 551 | 20 | 80 |
| 2015 | 1 | 572 | 60 | 80 |
The whole reason for the query was just to get the calculation of the "TotalBal" column, but as you can see the value is repeated along all records.
Is it possible to modify the query to only select one of these records? for instance only the record WHERE ConcatField = 551
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oh , i see you want running sum like
Period | Period ID | ConcatField | Balance | TotalBal |
|--------|----------------
| 2015 | 1 | 551 | 20 | 20 |
| 2015 | 1 | 572 | 60 | 80 |
please verify.
Period | Period ID | ConcatField | Balance | TotalBal |
|--------|----------------
| 2015 | 1 | 551 | 20 | 20 |
| 2015 | 1 | 572 | 60 | 80 |
please verify.
ASKER
Hello Experts, Thank you for your posts!
eghtebas, I was trying to change the original query in a way that it will only show me one record. I was able to accomplish this with fraigor recommendations. Thank again for helping out.
eghtebas, I was trying to change the original query in a way that it will only show me one record. I was able to accomplish this with fraigor recommendations. Thank again for helping out.
LuiLui77
this is a very strange new item given the history of this query so far....
further unnecessary complication has been to the query
and now BOTH columns containing balance WILL BE THE SAME VALUE....
see: http://sqlfiddle.com/#!3/d745a/8
this is a very strange new item given the history of this query so far....
further unnecessary complication has been to the query
and now BOTH columns containing balance WILL BE THE SAME VALUE....
| Period | Period ID | ConcatField | Balance | TotalBal |
|--------|-----------|-------------|---------|----------|
| 2015 | 1 | 572 | 80 | 80 |see: http://sqlfiddle.com/#!3/d745a/8
WHERE [Period ID] = 1
AND ConcatField IN ('551', '572')
to
WHERE [Period ID] = 1
AND ConcatField IN ('551')
or
WHERE [Period ID] = 1
AND ConcatField = '551'