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

[SeanK]

I have the following query (made generic for posting purposes)

gstrSQL = "SELECT SUM(field1) AS est, SUM(field2) AS act,  "
gstrSQL = gstrSQL & "field3, field4, field5, field6, "
gstrSQL = gstrSQL & "field7, field8, field9, field10, field11, field12 "
gstrSQL = gstrSQL & "FROM tablename
gstrSQL = gstrSQL & "WHERE [criteria]
gstrSQL = gstrSQL & "AND [More criteria]
gstrSQL = gstrSQL & "GROUP BY field3, field4, field5, field6, "
gstrSQL = gstrSQL & "field7, field8, field9, field10, field11, field12 "
gstrSQL = gstrSQL & "ORDER BY field5"
rs.Open gstrSQL, cn

When I run this, the numeric fields on which I'm doing a SUM return 0. However, when I do this

gstrSQL = "SELECT SUM(field1) AS est, SUM(field2) AS act
gstrSQL = gstrSQL & "FROM tablename
gstrSQL = gstrSQL & "WHERE [criteria]
gstrSQL = gstrSQL & "AND [More criteria]
rs.Open gstrSQL, cn
gstrSQL = "SELECT field3, field4, field5, field6, "
gstrSQL = gstrSQL & "field7, field8, field9, field10, field11, field12 "
gstrSQL = gstrSQL & "FROM tablename
gstrSQL = gstrSQL & "WHERE [criteria]
gstrSQL = gstrSQL & "AND [More criteria]
gstrSQL = gstrSQL & "ORDER BY field5"
rs.Open gstrSQL, cn

I get my correct totals and all of the data in the fields that I need.

My question is: Why do I need to run two queries to return, what to me, is a single 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.

[SeanK]

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.

Hmmm...so you are saying that this would never, ever work

gstrSQL = "SELECT SUM(field1) AS est, SUM(field2) AS act,  "
gstrSQL = gstrSQL & "field3, field4, field5, field6, "
gstrSQL = gstrSQL & "field7, field8, field9, field10, field11, field12 "
gstrSQL = gstrSQL & "FROM tablename
gstrSQL = gstrSQL & "WHERE [criteria]
gstrSQL = gstrSQL & "AND [More criteria]
gstrSQL = gstrSQL & "GROUP BY field3, field4, field5, field6, "
gstrSQL = gstrSQL & "field7, field8, field9, field10, field11, field12 "
gstrSQL = gstrSQL & "ORDER BY field5"
rs.Open gstrSQL, cn

Ok...it doesn't, so I can buy that, but, isn't there some way to sum two rows in a multitude of records without having to run two queries?

I doing an aggregate function, which means I HAVE to list all the fields in my SELECT that I'm NOT doing an aggregate on in my GROUP BY clause. Isn't this correct?

[szlamany]

The SUM()'s are unrelated to the primary query - if the primary query has GROUP BY

That means a sub-query would be needed - actually two.

Select (Select Sum(xyz) From...Where) as est
  ,(Select Sum(abc) From...Where) as act
  ,field1
  ,field2
...from...where...
Group by field1, field2

But that still begs to ask - why do you want repeated in every row that you are grouping a figure that has nothing to actually do with that row - but is instead related to the entire recordset?

[SeanK]

The SUM()'s are unrelated to the primary query - if the primary query has GROUP BY

That means a sub-query would be needed - actually two.

Select (Select Sum(xyz) From...Where) as est
  ,(Select Sum(abc) From...Where) as act
  ,field1
  ,field2
...from...where...
Group by field1, field2

I will give this a shot and see what happens.

But that still begs to ask - why do you want repeated in every row that you are grouping a figure that has nothing to actually do with that row - but is instead related to the entire recordset?

I don't understand this question. I need 12 fields worth of data to be returned. I need to total two of those fields, so does not the rules of SQL dicate that the other 10 fields MUST be in a GROUP BY clause?

[szlamany]

Well - I see GROUP BY as an integral part of SUM() logic.

Let's say I have a GENDER field in a table - M for Male, F for Female. Total of 55 rows in the table...

If I go Select Gender,Sum(1) From SomeTable Group By Gender I will get

M 25
F 30

The 25 is related to the M rows - the 30 is related to the F rows. I would not expect to see the 55 anywhere in that row.

If I need to see the total number of people - then a SUBQUERY gets that.

Select Gender,Sum(1)
,(Select Sum(1) From SomeTable)
From SomeTable Group by Gender

Now I get

M 25 55
F 30 55

I could see doing that if I was going to then do a calc in the client side for the percentage of MALE vs FEMALE, for instance...

I don't know what your business requirements are - so I can only speculate.

[SeanK]

Well - I see GROUP BY as an integral part of SUM() logic.

Let's say I have a GENDER field in a table - M for Male, F for Female. Total of 55 rows in the table...

If I go Select Gender,Sum(1) From SomeTable Group By Gender I will get

M 25
F 30

The 25 is related to the M rows - the 30 is related to the F rows. I would not expect to see the 55 anywhere in that row.

If I need to see the total number of people - then a SUBQUERY gets that.

Select Gender,Sum(1)
,(Select Sum(1) From SomeTable)
From SomeTable Group by Gender

Now I get

M 25 55
F 30 55

I could see doing that if I was going to then do a calc in the client side for the percentage of MALE vs FEMALE, for instance...

I don't know what your business requirements are - so I can only speculate.

This is related to a Budget Prep system I'm working on.

Each Cost Center has a budget for specific lines items. When entered their budget, they enter an estimated cost for each line item. As the year ends, and they start preparing for their next budget, each line item needs to be updated with the actual cost.

My report needs to gather all pertinent data for each Cost Center (Cost Center = Criteria #1) Each line item, along with its estimated cost, actual cost, prepared by, updated by, date prepared, date updated, line item name, department name, division name, line of business, FOB Code, and MDI Code (both codes are used internally), need to be displayed for each line item. Hence, the 12 fields.

At the bottom of the report, I have to have a total, for all line items, of estimated and actual cost (along with a variance which I have already worked out the code for.)

So, I need to display all 12 fields, and the totals for two of them.

I am doing a SUM on estimated code, a SUM on actual cost, and a GROUP BY all the rest so I won't get any SQL errors for not grouping by the fields in my SELECT query that I'm NOT doing an aggregate function on.