Firstly it would have helped if you had explained that the "comma" in "20766,31575" is the decimal point as per German notation not English.
I created a small table based on a subset of your data as follows
create table table1(
ProjectNr varchar(20),
IstValue float,
SollValue float
)
insert into table1 values('D.01025',0, 19010)
insert into table1 values('D.01025',0, 0)
insert into table1 values('D.01025',0, 409196)
insert into table1 values('G.01013',20766.31575, 20406.21832)
insert into table1 values('G.01004',2210.34804, 2184.59583)
insert into table1 values('G.01004',637.73126, 637.38993)
insert into table1 values('G.01004',1557.47348, 1424.94381)
insert into table1 values('G.01004',745.29877, 745.93531)
As you say, the query you presented in your post
SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[SollValue]) as Soll
FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2 ON t1.[SollValue] = t2.[IstValue]
WHERE t2.[IstValue] IS NULL
GROUP BY t1.[Projectnr], t1.[SollValue]
ORDER BY t1.[Projectnr] ASC
gives the following results when run against my subset
PROJECTNR TOTALOFPROJ SOLL
D.01025 1 19010
D.01025 1 409196
G.01004 1 637.38993
G.01004 1 745.93531
G.01004 1 1424.94381
G.01004 1 2184.59583
G.01013 1 20406.21832
The reason you are only getting counts of 1 is because you have included
Soll_Value
in the
GROUP BY
and they are all unique. Remove it from the Group By as it is totally unnecessary - you are only displaying an aggregate of Soll_Value not the column itself. Giving you this query
SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[SollValue]) as Soll
FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2
ON
t1.[SollValue] = t2.[IstValue]
WHERE t2.[IstValue] IS NULL
GROUP BY t1.[Projectnr]
ORDER BY t1.[Projectnr] ASC
which yields these results
PROJECTNR TOTALOFPROJ SOLL
D.01025 2 428206
G.01004 4 4992.86488
G.01013 1 20406.21832
That self join serves no purpose either so the following query will give exactly the same results:
SELECT Projectnr, count(Projectnr) AS TotalOfProj, sum(SollValue) as Soll
FROM [dbo].[table1]
WHERE IstValue <> SollValue
GROUP BY Projectnr
ORDER BY Projectnr ASC
Edit - removed the bit about the self-join after reading the OP's other question - self-join is an efficient way of applying the filters that are actually required