How to count and sum records using self join

Question

0.00/5 (No votes)

See more:

Hi I' m having problems counting and summing up my records. Here an example and the possible outcome:

ProjectNr	Ist Value	  Soll 
D.01025	          0	          19010
D.01025           0	          0
D.01025	          0	          409196
G.01013	       20766,31575	  20406,21832
G.10002	       3409,73749	  3707,07765
G.10004	       8052,97646	  8057,03358
G.01006	       1,50104	          1,50104
G.00960	       585385,8582	  543435,6963
G.01961	       11000	          0
G.01004	       2210,34804	  2184,59583
G.01004	       637,73126	  637,38993
G.01004	      1557,47348	  1424,94381
G.01004	       745,29877	  745,93531
G.01043	      29285,59879	 28600,87531
G.01162	      23226	          0
G.01069	     72765,80058	73450,86162

OUTCOME:
ProjectNr	Nr. of rows	Soll Value
D.01025	            3             428206
G.01013             1             20406,21832
.
.
G.01004             4              5001,84

My statement below but it isn't corporating

SQL

SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[Soll_value]) as Soll
 
FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2
ON
t1.[Soll_value] = t2.[Ist_value]
WHERE t2.[Ist_value] IS NULL
GROUP BY t1.[Projectnr], t1.[Soll_value]
ORDER BY t1.[Projectnr] ASC

Posted 13-Jan-15 0:05am

mikybrain1

Updated 13-Jan-15 1:18am

King Fisher

v3

Add a Solution

Comments

CHill60 13-Jan-15 6:07am

What do you mean by not cooperating??

mikybrain1 13-Jan-15 6:11am

HiI mean instaed of the outcome, i'm getting the result like

D.01025 1 19010
D.01025 1 0
D.01025 1 409196
.
.
.

it's counting each Projectnr one by one instead of aggregating it and sum the soll value relating to the projectnr

CHill60 13-Jan-15 6:25am

It's not aggregating because you have included [Soll_value] in the GROUP BY clause. And you can't sum things with commas in them - they'll be varchars

mikybrain1 13-Jan-15 6:49am

If i remove the soll value in the group by then i get error 8120.
That means i need to add it there.

CHill60 13-Jan-15 7:07am

Really?? 8120 only occurs if you attempt to retrieve a value that is not an aggregate or is not in the group by - you are not retrieving soll_value you are using SUM() on it. But as I said, you can't sum a varchar and you can't have commas in numeric values.
What are those columns in the sample data?

mikybrain1 13-Jan-15 7:23am

The columns are ProjectNr, Ist_value and Soll_value.
Ok i have this question as a statement which is running good
1.What soll-values are not in Ist value

BUT
I wanna have a ProjectNr(D.01025)
How many time it occurs (let say 3 times)
And sum all Soll values relating to it.
Something like this

D.01025 3 428206

My statement is ok but it' not counting and suming.

CHill60 13-Jan-15 7:54am

What datatype is soll_value - you can't have commas in numeric values and you cannot sum varchars. Or are they a list of values from another table, or what?

mikybrain1 13-Jan-15 8:30am

It' a float or just ignore the commas

CHill60 13-Jan-15 9:32am

The solution I've posted sorts out the problem you have stated here, ~~but I've now just read your earlier question and I don't think what you have here (nor the solution to your earlier post) actually achieve what you want.~~ I've looked again and the self join is actually more efficient - the poster of the solution used columns that didn't exist which confused me at first

mikybrain1 13-Jan-15 10:22am

Thnx very very much for your patience

mikybrain1 14-Jan-15 3:00am

Hi CHill60
I've a pardon for u. Can u please help me with my new post?
http://www.codeproject.com/Questions/864495/How-to-get-all-differences

Kornfeld Eliyahu Peter 13-Jan-15 6:12am

Why the join? A simple group by on ProjectNr should do...

mikybrain1 13-Jan-15 6:19am

HiBecause i'm trying to have the solution to the ff questions

1.What soll-values are not in Ist value
2.What Ist values are not in Soll values
3.What soll values are in Ist
4.What Ist values are in Soll

And because the projectnrs repeats, i wanna count and sum it values

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-01-13T03:25:00

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

SQL

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

SQL

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

SQL

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:
SQL

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