Using SUM, why do I need two queries for one result

[SeanK]

It has been a full 24 hours since I first posted this so I’m hoping the mods don’t get upset because I’m bumping it.

Then again, perhaps there is no reason to bump it. As it is a full 24 hours old, had several views, but no responses, I’m inclined to believe that there is no way to do it other than my solution.

What I have works. The totals are correct, the data is all on the report, and my users are happy. It just seems there would have to be a better way.

But, if not, I can live with this.

[Ecniv]

As a guess ('cause I dunno what filters etc there were).

The first should return totals if all the fields 3-12 are the same for all records.
You got 0 because of the filters (probably).

One way to check is to (re)create the totals first query, but supply no filters. See if it returns totals, especially if they are right. then add in one or two of your filters until you get back to 0.


It does seem weird that you need to get the totals in one query and the data to display in the other...
Was field5 unique - 'cause otherwise the totals lines may not match?

Anyway- good luck fixing what is not broke but ensuring your happiness with it

[szlamany]

I also cannot believe that this was required - the SUM() should work...

How many rows were returned in the first example?

Do you have nulls in any of these fields?

What is the back end DB?

[SeanK]

How many rows were returned in the first example?

One row with two fields. One containing the sum of field1 and one containing the sum of field2

Do you have nulls in any of these fields?

Not in the query doing the totals, but in the query returning the remainder of the data, yes. Which fields are empty, or NULL, will depending entirely on the selection criteria, which will differ each time the query is run.

What is the back end DB?

SQL Server 2000

Also, when I run the first query (doing the SUMs and gathering the data) from Query Analyser, I also get 0 zeros for the totals.

[szlamany]

If only one row is returned why ORDER BY FIELD5?

I would love to get my hands on the data in that table - can you post it?

If you go into ENTERPRISE MANAGER and open that table you can select all the rows and columns and copy/paste it into a .TXT file...

[SeanK]

If only one row is returned why ORDER BY FIELD5?

By this I meant when I do that SUM query all by itself I get one row, and I don't use an ORDER BY when I run that. When I put the two together, as the first code example in Post #1, I get a multitude of records. The number of records returned, as I said, will vary depending on the criteria in the WHERE and AND clauses. Regardless, however, I still need those two columns totaled, as well as all of the rest of the records.

I would love to get my hands on the data in that table - can you post it?

If you go into ENTERPRISE MANAGER and open that table you can select all the rows and columns and copy/paste it into a .TXT file...

I don't think my management would be real happy if I did that.

[szlamany]

The SUM() without the GROUP BY is a SUM() of the entire table - but limited by the WHERE clause.

The SUM() with a GROUP BY gives a different SUM() for each row - a SUM() related to the GROUP BY fields.

So I do not understand how you can be doing both - or even trying to do both.