CODEWITHSUNDEEP
sqlsql-server
user1741874
user1741874

Reputation:

SQL Server Aggregates and Sum

having a problem with a SQL query and can't seem to figure it out. Pretty sure I'm going about it all wrong.

Basically, I have PRVDR_NUM and ITM_VAL. I Want to sum up the related ITM_VALS so each PRVDR_NUM just has one "summed up" ITM_VAL [SumReimb is the alias].

PRVDR_NUM is actually in another table; I related to it using an inner join of RPT_REC_NUM, which is in both tables.

Here's a very not working example.

SELECT  PRVDR_NUM,SUM(ITM_VAL) as SumReimb FROM hha2011num
INNER JOIN hha2011rpt ON hha2011num.RPT_REC_NUM = hha2011rpt.RPT_REC_NUM 
WHERE (WKSHT_CD='D000000' AND LINE_NUM = '01201') AND (CLMN_NUM='0100' OR CLMN_NUM='0200')

GROUP BY PRVDR_NUM,ITM_VAL ORDER BY ITM_VAL

The main thing I'm summing is CLMN_NUM 0100 and 0200, each have a different item value and I want to add them up and then group by the PRVDR_NUM.

Any suggestions?

Main problems I'm having in SQL is GROUP, Aggregates, and Calculated fields. I'm not sure how to... tie it all together. I started learning SQL about two days ago.

EDIT BELOW THIS LINE------------

Here's two different but should be the same queries.

SELECT  RPT_REC_NUM,SUM(ITM_VAL) SumReimb FROM hha2011num
WHERE (WKSHT_CD='D000000' AND LINE_NUM = '01201') AND CLMN_NUM in('0100','0200')

GROUP BY RPT_REC_NUM ORDER BY SumReimb

SELECT  PRVDR_NUM,SUM(ITM_VAL) as SumReimb FROM hha2011num
INNER JOIN hha2011rpt ON hha2011num.RPT_REC_NUM = hha2011rpt.RPT_REC_NUM 
WHERE WKSHT_CD='D000000' AND LINE_NUM = '01201' AND CLMN_NUM in('0100','0200')
GROUP BY PRVDR_NUM ORDER BY SumReimb

Now; most of the data between them IS the same, except one has 39 more entries than the other [Top one does]. Which is probably why just a handful of the data in the second one are orders of magnitude higher than I expect.

Upvotes: 1

Views: 84

Answers (1)

Sonam
Sonam

Reputation: 3466

SELECT  PRVDR_NUM,SUM(ITM_VAL) as SumReimb FROM hha2011num
INNER JOIN hha2011rpt ON hha2011num.RPT_REC_NUM = hha2011rpt.RPT_REC_NUM 
WHERE WKSHT_CD='D000000' AND LINE_NUM = '01201' AND CLMN_NUM in('0100','0200')
GROUP BY PRVDR_NUM ORDER BY PRVDR_NUM

Please remove ITM_VAL from the group by and order by clause. Also, I've made one small change in the where clause, instead of using or for checking the value of column CLMN_NUM, you can use "IN"

Upvotes: 2

Related Questions