How do I create a new table based on a with query-CodePudding

I have a table that includes a JSON. I want to get a certain parameter of the JSON and remove the € sign out of this JSON (so I get a numeric value that I can sum).

That's the base query that works:

With C as (
SELECT      A.identifier, 
                JSON_VALUE(A.jsonBody,'$.path) as somethingA,
                JSON_VALUE(A.jsonBody,'$.path) as somethingB
FROM table A WITH(NOLOCK)
join table B on (A.identifier = B.identifier)
WHERE A.statement = 'x')

select  C.identifier, 
    replace (C.fieldA, '€','') as TotalA, 
    replace (C.fieldB, '€','') as TotalB
from C)

Unfortunately I am now stuck as I want to get the sum of TotalA per identifier.

As described my query works but I want to get the sum per identifier. So I thought I'd be an idea to simply create (create xxx as (query)) but I get an error.

Anyone has an idea on

how I can improve this query in general (replace JSON_Value in one commmand?)
how I can get the TotalA, TotalB amount per identifier?

CodePudding user response：

To get the sum of TotalA per identifier, you can wrap the previous query in another subquery and group by identifier:

WITH C AS (
SELECT A.identifier,
JSON_VALUE(A.jsonBody,'$.path) as fieldA,
JSON_VALUE(A.jsonBody,'$.path) as fieldB
FROM table A WITH(NOLOCK)
JOIN table B ON (A.identifier = B.identifier)
WHERE A.statement = 'x'
), D AS (
SELECT C.identifier,
REPLACE(C.fieldA, '€','') as TotalA,
REPLACE(C.fieldB, '€','') as TotalB
FROM C
)
SELECT identifier, SUM(TotalA) AS SumOfTotalA
FROM D
GROUP BY identifier;

As for improving the query, if you want to combine the JSON_Value and the replace into one command, you can use the following format:

WITH C AS (
SELECT A.identifier,
CAST(REPLACE(JSON_VALUE(A.jsonBody,'$.path'), '€','') AS decimal(10,2)) as TotalA,
CAST(REPLACE(JSON_VALUE(A.jsonBody,'$.path'), '€','') AS decimal(10,2)) as TotalB
FROM table A WITH(NOLOCK)
JOIN table B ON (A.identifier = B.identifier)
WHERE A.statement = 'x'
)
SELECT identifier, SUM(TotalA) AS SumOfTotalA
FROM C
GROUP BY identifier;

Note that you need to cast the JSON value as a decimal with 2 decimal places to make sure the replace function works as expected.