holemania
asked on
SQL Query - Repeating a Row
I have a table called "ACCOUNT_TEMP" that has some data in there.
Here's what my raw table looks like.
From the output example, the column F is what I'm after. I am able to get all of it with my query, but I need to roll up all the dollar amount in Column E into account "15-200" with the data center.
As you can see, from the above example, the items highlighted, I need to repeat the account "15-200". This is to roll up all the rows with a value greater than zero in the "Sales Amt" column with the respective "Data Center". Data Center "K00200" needs to always be first and data center "K00380" always needs to be last. How would I go about doing this?
Here's what I was able to get with my query, but can't seem to get the highlighted yellow portion and need help.
My sql query.
Here's what my raw table looks like.
From the output example, the column F is what I'm after. I am able to get all of it with my query, but I need to roll up all the dollar amount in Column E into account "15-200" with the data center.
As you can see, from the above example, the items highlighted, I need to repeat the account "15-200". This is to roll up all the rows with a value greater than zero in the "Sales Amt" column with the respective "Data Center". Data Center "K00200" needs to always be first and data center "K00380" always needs to be last. How would I go about doing this?
Here's what I was able to get with my query, but can't seem to get the highlighted yellow portion and need help.
My sql query.
SELECT A.ACCOUNT_ID, A.DESCRIPTION, A.TOTAL_BALANCE, A.DATA_CENTER, A.SALES_AMT,
CASE WHEN A.ACCOUNT_ID = '10-200' AND A.DATA_CENTER = 'K00300'
THEN (A.TOTAL_BALANCE - B.SALES_AMT)
ELSE (A.TOTAL_BALANCE + A.SALES_AMT)
END NEW_TOTAL
FROM ACCOUNT_TEMP A
LEFT OUTER JOIN
(
SELECT A.ACCOUNT_ID, SUM(A.SALES_AMT) SALES_AMT
FROM ACCOUNT_TEMP A
GROUP BY A.ACCOUNT_ID
) B ON B.ACCOUNT_ID = A.ACCOUNT_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to resolve the issue myself.
why do we have to start at the table ACCOUNT_TEMP and not earlier?
what is the code that produces ACCOUNT_TEMP?
Is there some hierarchy data available to the query?
can you provide some relevant account_temp data in a reusable format please