I have a simple web page , i am using two tables just like below
First Table
| RecordID | Total Marks | Achieved Marks | Country_ID(foreign_key) |
| 1 | 40 | 11 | 1 |
| 2 | 41 | 14 | 1 |
| 3 | 100 | 56 | 2 |
| 4 | 12 | 11 | 1 |
| 5 | 100 | 99 | 3 |
| 6 | 187 | 56 | 2 |
| 7 | 67 | 56 | 1 |
Second Table
| RecordID | Country_ID | Country_Name |
| 1 | 1 | PAKISTAN |
| 2 | 2 | INDIA |
| 3 | 3 | AUS |
| 4 | 4 | Nezland |
| | | |
I want a dynamic Stored procedure no matter how much countries i have in my First table I want to get the Sum of Total Marks Vs Sum of Achieved marks
Like for Country =1
Total(SUM)=160
Achieved(SUM)=92
I want a stored procedure which calculates the Sum of Total Marks vs Achieved Marks from First table not matter how much countries are there , if i have records for 50 contries then it should show the SUm of Total vs sum of Target for these 50 countries how to write that kind of stored procedure right now i am using static queries by using multiple select statement and giving the country ID in where clause , I am making crystal report and i want to show the Total (SUM) VS Achieved(SUM ) on chart like that