Group by problem

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

ApeX

i have the following table:

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


the query is:

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

.....if 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

....the problem is......
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!!
 
Try this, perhaps:

SELECT A.partner, A.PartnerName, (SELECT SUM(T.Duguje)
FROM arhivdugujepotrazuje AS T
WHERE T.datumdos <= '01-01-2007' and
T.partner = A.partner and T.PartnerName = A.PartnerName)
- SUM(Potrazuje) As Dospjelo FROM arhivdugujepotrazuje
AS A
group by A.partner, A.PartnerName
 
Back
Top