0
SELECT sum(cte.TARGET) "target" , sum(cte.ACHIEVED) "achieved" FROM (SELECT SUM(TargetValue) AS TARGET ,SUM(AchievedValue) AS ACHIEVED FROM First F
INNER JOIN Second AS S ON S.QuestionID=F.QuestionID
Where 1=1
group by S.RecordID)cte
0 it is giving the wrong result and meanwhile it does not allow to apply where clause
0 Hi,
Select Sum(Target) from FirstTable
Union
Select Sum(Archived) from SecondTable
0 I want SUM OF BOTH TARGET AND ACHIEVED ,
| QUESTIONID(P) | TARGET |
| 1 | 200 |
| 2 | 300 |
Second table
| RecordID(P) | QuestionID(F) | Achieved |
| 1 | 1 | 10 |
| 2 | 1 | 200 |
| 3 | 2 | 300 |
I WANT SOME OF TARGET and SUM OF ACHIVED like
SUM OF TARGET SHOUD BE (500) and SUM OF ACHIEVED SHOULD BE (510)
0 Table First
QuestionID(primaryKey) TargetValue
1 100
2 500
3 200
Second Table
RecordID(primaryKey) Achieved Value QuestionID(foreignkey)
1 14 1
2 14 1
3 16 2
As per Manas told
Please find below query if you want to get 100:
SELECT SUM(Achieved) AS ACHIEVED, [Target]
FROM tabl1
INNER JOIN tabl2 on tabl2.QuestionID=tabl1.QuestionID
group by tabl1.QuestionID, tabl1.Target
1-I want to calculate SUM of Target and SUm of achieved not just target and sum of achieved
I want while calculating SUM of Target
SELECT SUM(QuestionTarget) will takes 100 regardless of how many rows in the second table against question 01 ....
if inner join create problem i have tried left outer join as well it also not giving the desired result
0 Hi Danish,
your query is wrong. with your expectation, below query would suit your requirments
SELECT F.QuestionID, TargetValue, , Sum(Archived Value)
FROM FirstTable F INNER JOIN SecondTable S ON F.QuestionID = S.QuestionID
Where F.QuestionID = 1
Group by F.QuestionID, TargetValue
Order by F.QuestionID
0 First TAble
| QuestionID | Target Value | Men | Women | Boy | Girl | CITY | |
| 1 | 200 | 100 | 25 | 25 | 50 | LAHORE | |
| 2 | 400 | | | | | LAHORE | |
Second Table
| QuestionID(Foreignkey) | Achived Value | Village | |
| 1 | 100 | First | |
| 1 | 200 | Second | |
The problem is that i want to get the sum of Target and sum of achieved values and also want to give the parameters @QuestionID for which i want the sum in crystal report
the issue is that if i have one row in first table for question ID 1 and two rows in second table for question ID 1 when i want to calculate the sum of Target vs sum of achived
my Answer table has two rows so due to inner join it return two rows and it adds my target value two times for question id =1
SELECT SUM(ISNULL(0,TargetValue) from First AS F
INNER JOIN Second AS S ON S.QuestionID=F.QuestionID
so my Target value sum becomes 400 instead of just 200 due to two rows in second table ..
0 SELECT mt.ID, COUNT(d.MasterID) as [#Count]
FROM MasterTable mt
INNER JOIN Detail d on mt.ID = d.ID
GROUP BY mt.ID
ORDER BY mt.ID
0 My issue is just when i join using inner join the join append the values for all rows comming from second table so the sum is incorrect mean that if i have 30 rows in second table and one row in the first table and when i inner join all the thirty rows gets value of first row from the first table
0 I have tried listen to me once more, I have a table in which i have a target value for user that they have to achieve this target for example "Build new schools in village " I give them a target of 100 for whole City , Now he/she has 300 villages in this city so he has to submit his achivement against these targets so 300 rows when i inner join and try to make the sum of target value vs sum of achieved value all 300 rows in achived table gets value of 100 so how to get that ...
0 Hi Dhanish,
SELECT mt.ID, mt.NAME, mt.AGE, COUNT(d.MasterID) as [#Subjects]
FROM MasterTable mt
LEFT OUTER JOIN Detail d on mt.ID = d.ID
GROUP BY mt.ID, mt.NAME, mt.AGE
ORDER BY mt.ID
Hope this might help you. If it suits your requirement, pls mark it as answered.
0 Hi Danish,
Please share the Query that is not showing relevent results