SUM() issue

  • Thread starter Thread starter ApeX
  • Start date Start date
A

ApeX

Hi, i have a question for y'all, i have a table

D P Partner
---------------------------
1 3 P
5 2 P
6 4 K
8 3 K
7 8 P
7 5 K
2 3 K

Ok, what i need, is to find a difference between the SUM(of D column
where the Partner is P) and SUM(of P column for all partners)...

So, the result should be SUM(D where Partner = P) - SUM(P)

If anyone has any ideas, please share it with me,....THANX!!
 
ApeX said:
Hi, i have a question for y'all, i have a table

D P Partner
---------------------------
1 3 P
5 2 P
6 4 K
8 3 K
7 8 P
7 5 K
2 3 K

Ok, what i need, is to find a difference between the SUM(of D column
where the Partner is P) and SUM(of P column for all partners)...

So, the result should be SUM(D where Partner = P) - SUM(P)

If anyone has any ideas, please share it with me,....THANX!!
Click to expand...

ApeX,

Table:

Create a blank MS Access database. You can copy and paste this DDL SQL query into an MS
Access Query, executing it in order to create the table.


I could not detemine your primary key, so I added a column for that.

CREATE TABLE DPPartner
(DPPartnerID AUTOINCREMENT
,D INTEGER
,P INTEGER
,Partner TEXT(1)
,CONSTRAINT pk_DPPartner
PRIMARY KEY (DPPartnerID)
)


Sample Data:

As above.


Query:

SELECT (SELECT SUM(D0.D)
FROM DPPartner AS D0
WHERE D0.Partner = "P")
-
SUM(D1.P) As Difference
FROM DPPartner AS D1

Results:

Difference
-15


Sincerely,

Chris O.
 
SELECT Partner
, (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
FROM TheTable
GROUP BY Partner

Should Return
Partner : TheDiff
K : -13
P : -23

If you want to restrict it to on specific partner then add a where clause
SELECT Partner
, (Sum(D) - (SELECT Sum(P) TheTable)) as TheDiff
FROM TheTable
WHERE Partner = "P"
GROUP BY Partner

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

Any idea how Access actually processes this (query plan)? I would think that
it would process the (SELECT SUM(P) FROM TheTable) section for each grouping.
Would it be more efficient (faster) to do something like:

SELECT yourTable.Partner, Sum(yourTable.D) - Tot.SumofP as theDiff
FROM yourTable, (SELECT SUM(P) as SumOfP FROM yourTable) as Tot
GROUP BY yourTable.Partner

I would guess that Access will process the subquery only once, in this case.

Dale
 
You may be and probably are correct. It would depend on how well the query
interpreter was written. The interpreter might be smart enough to figure
out that the subquery was not correlated and only run it one time.

And I note that my SQL is missing the word "FROM" in the calculated field
which should have read
(SELECT Sum(P) FROM TheTable)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Duguje Potrazuje Partner PartnerName Date

-------------------------------------------------------------------------------------
1 3 P Tisak
12.3.2006
5 2 P Croatia
25.6.2006
6 4 K HT
15.6.2007
8 3 K Tisak
11.11.2005
7 8 P Croatia
3.5.2007
7 5 K HT
11.3.2005
2 3 K HT
5.1.2005

Ok, what i need, is to find a difference between the SUM(of D column
where the Partner is P) and SUM(of P column for all partners)...

So, the result should be SUM(D where Partner = P) - SUM(P)

-----------------------

Hey guys, thanx i figured it out, at least i think :)), ok now i
changed the table a bit, so the query looks now

SELECT partner, PartnerName, (SELECT SUM(Duguje)
FROM arhivdugujepotrazuje
WHERE datumdos <= '01-01-2007' and
partner = 'p' and PartnerName = 'croatia')
-
SUM(Potrazuje) As Dospjelo FROM arhivdugujepotrazuje
where partner = 'p' and PartnerName = 'croatia'
group by partner, PartnerName


.....but the problem is following, when i put the 'partner = 'some
name' and PartnerName = 'some name' i get what what i want, it is:

the result:

partner PartnerName dospjelo
------------------------------------------------
P Croatia -5

....
but when i don't wanna put the condition "where" with the partner and
partnername, that is, when i wanna show all grouped partner &
partnername i get some strange results :

example:

SELECT partner, PartnerName, (SELECT SUM(Duguje)
FROM arhivdugujepotrazuje
WHERE datumdos <= '01-01-2007')
-
SUM(Potrazuje) As Dospjelo FROM arhivdugujepotrazuje
group by partner, PartnerName


partner PartnerName dospjelo
------------------------------------------------
P Croatia 13
K HT 11
K Tisak 20
P Tisak 20


If anyone has any ideas, please share it with me,....THANX!!
 
Back
Top