Subqueries and grouping problem

[sowhatnow]

I need help by today if possible.
I have a table called TABLE1 containing sourceid, contactno, glno, totalamount

I need to pull all the sourceid where the totalamount =0 for the grouping of contactno and glno.

Here is what I have so far but it keeps erroring out.

select sourceid from tempForARTable3
where (select contactno, glno from tempForARTable3
group by contactno, glno
having sum(totalamount)=0)

oops I forget to say that it is a T-SQL for a vb application. But hopefully someone can help.

 

[RiazKhanmohamed]

surely you could just use:

select sourceid from tempForARTable3
where sum(totalamount)=0
group by sourceid, contactno, glno

also, if youre table is called table1, why are you looking at tempforartable3?

 

[sowhatnow]

Hi Fred,
Thanks for the catch. It is table tempForARTable3 and your solution did not work. I got an error.

Server: Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

 

[RiazKhanmohamed]

oops, yeah it would. try this:

SELECT sourceid
FROM tempForARTable3
GROUP BY sourceid, contactno, glno
HAVING (((Sum(totalamount))=0));

 

[sowhatnow]

Hi,
No errors but still didn't work. I should have had two sourceid displayed.

 

[RiazKhanmohamed]

seems to work for me. what data have you got in your table? give me a sample of the data in it

 

[RiazKhanmohamed]

seems to work for me. what data have you got in your table? give me a sample of the data in it.

also, what are the column types?

 

[sowhatnow]

Hi,
sourceid contactno glno totalamount
(int) (varchar) (varchar) (money)
265 8652240 AR 42.1
269 8655704 AR 13
219 4530329 AR 315
344 0000001 PCP 1
345 0000001 PCP -1
207 4264319 AR 79.02
247 6632467 AR 32.4

sourceid 344 and 345 should be displayed
Thank you for all your help.

 

[RiazKhanmohamed]

ah, now hang on. you want all sourcid which add up to 0. this will only show if one id adds up to 0, so if you had another entry for 345 which had 1 then that would show up, because the total is 0. what you want can't be done in a single query.

 

[sowhatnow]

Hi,
Actually, I want all the sourceid for
the grouping of contactno and glno which totals to 0.
example: 344 and 345 shows up because their contactno 0000001 and glno PCP summed together (1 + -1) = 0. Can it be done?

 

[RiazKhanmohamed]

i get you. you need two queries if you want both.

one query should look like this:

SELECT glno, Sum(totalamount) AS SumOftotalamount
FROM tempForARTable3
GROUP BY glno;

Then query on that:

SELECT tempForARTable3.sourceid, [glno filter].SumOftotalamount
FROM [glno filter] INNER JOIN tempForARTable3 ON [glno filter].glno = tempForARTable3.glno
WHERE ((([glno filter].SumOftotalamount)=0));

 

[sowhatnow]

Hi,
Sadly, no this doesn't work either. I get an error:

Invalid object name 'glno filter'.

Thank you for all your help.

 

[RiazKhanmohamed]

yeah sorry. you have to replace glno filter with the name of the first query. i called the first one glno filter, hence using it above. sorry for the confusion

 

[sowhatnow]

Hi,
okay now I'm really feeling inadequate. How do I name a query in SQL query (I'm using SQL server and stored procedure)?

Thank you for all your help.

 

[RiazKhanmohamed]

ah. thought you were using access.

use this:

CREATE VIEW #query_name_without_spaces#
AS

SELECT glno, Sum(totalamount) AS SumOftotalamount
FROM tempForARTable3
GROUP BY glno

that's it.

then run this sql/create a query as you would with this sql:

SELECT t.sourceid, v.SumOftotalamount
FROM #query_name_without_spaces# v INNER JOIN tempForARTable3 t ON v.glno = t.glno
WHERE (((v.SumOftotalamount)=0))


replacing the #query_name_without_spaces# with your query name (without spaces - sqls server don't like em)

it's probably easier to test in qa (query analyzer) you can run/create the query from there.

 

[sowhatnow]

Oh..you're a doll! Thank you ever so much!
We are excited over here. Now I have to understand views and what my customers can and can't do.