calculating sum in a view
-
There is a table called QCR100 containing the following information ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111200 investments 126000005 1630000000.0000 111600 Other Assets 133000009 1700000000.0000 111600 Other Assets 150000002 1730000000.0000 111701 Leasehold 190010130 1740000000.0000 111705 Furniture 190010036 1750000000.0000 111706 Computer Software 190010141 46995799.1800 Then I created a view with this command statement: SELECT CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1) = '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE FROM dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode How I can modify this view to sum the amount for any duplicated item code and group by item code Example ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111600 Other Assets 133000009 &nbs
-
There is a table called QCR100 containing the following information ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111200 investments 126000005 1630000000.0000 111600 Other Assets 133000009 1700000000.0000 111600 Other Assets 150000002 1730000000.0000 111701 Leasehold 190010130 1740000000.0000 111705 Furniture 190010036 1750000000.0000 111706 Computer Software 190010141 46995799.1800 Then I created a view with this command statement: SELECT CASE WHEN LEFT(e.dcode, 1) = '1' THEN isnull(SUM(a.Dr_bal_lcy - a.CR_BAL_LCY), 0) WHEN LEFT(e.dcode, 1) = '2' THEN isnull(SUM(a.CR_BAL_LCY - a.Dr_bal_lcy), 0) ELSE 0 END AS amount, e.ITEM_DESCRIPTION, e.ITEM_CODE FROM dbo.GLTEMP_CONS AS a RIGHT OUTER JOIN dbo.QCR100 AS e ON LTRIM(a.GL_code) = e.Dcode GROUP BY e.ITEM_DESCRIPTION, e.ITEM_CODE, e.Dcode How I can modify this view to sum the amount for any duplicated item code and group by item code Example ITEM_CODE ITEM_DESCRIPTION DCODE AMOUNT 111200 investments 24000003 1630000000.0000 111600 Other Assets 133000009 &nbs
This is really a SQL question, not a VB question and should probably belong in the General Database forum. That said, I'm having a difficult time trying to figure out what you're asking. Is it that your view is doing one level of aggregation, and you need a second (grouping then by item code)? If so, you can use the query you have as a subquery, nested inside another that performs that second level of aggregation: SELECT ITEM_CODE, SUM(...) FROM ( -- original query SELECT CASE WHEN LEFT(e.dcode,1) = '1' THEN ... ... ) x GROUP BY ITEM_CODE