SELECT and SUM 3

[Scott24x7]

Hi All,
I have a small problem I'm trying to solve with SELECT.
I thought I should be able to sum while issuing a select. When I issue the following command:

SELECT EXPENSETYPE, EXPENSEPURPOSE, SUM(expenseconvertedto) AS EXPACCUM FROM EXPENSEDETAIL WHERE EXPENSEID = 81

So my intent here is to get the Expense Purpose summed into a table or cursor (in this example I've forced in EXPENSEID = 81 so that it just looks at one expense report).

The problem is I get an error stating Missing or invalid GROUP BY clause. When I add:

SELECT EXPENSETYPE, EXPENSEPURPOSE, SUM(expenseconvertedto) AS EXPACCUM FROM EXPENSEDETAIL WHERE EXPENSEID = 81 GROUP BY EXPENSEPURPOSE

I get the exact same error message.
Is SUM not compatible with SELECT?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Hi Scott,

When you have a GROUP BY clause, the expressions in the SELECT list (that is, the list of expressions immediately after the word SELECT) must either be aggregate functions (of which SUM() is an example) or fields involved the grouping (in this case, ExpensePurpose). Your ExpenseType field does not meet that requirement.

In other words, if you remove ExpenseType, it should work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Gagnon]

[pre]SELECT EXPENSETYPE, EXPENSEPURPOSE, SUM(expenseconvertedto) AS EXPACCUM FROM EXPENSEDETAIL WHERE EXPENSEID = 81 GROUP BY EXPENSETYPE,EXPENSEPURPOSE
[/pre]



If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Alternatively, if you want to show ExpenseType in the result set, and if there is a one-to-one correspondence between ExpenseType and ExpensePurpose (that is, each ExpenseType belongs to the same ExpensePurpose, or vice versa), then you could do this:

SELECT Expensepurpose, [highlight #FCE94F]MAX([/highlight]ExpenseType[highlight #FCE94F])[/highlight], SUM(ExpenseConvertedto) AS Expaccum ;
    FROM ExpenseDetail WHERE ExpenseID = 81 ;
    GROUP BY ExpensePurpose

If there is no one-to-one correspondence between ExpenseType and ExpensePurpose, then you probably shouldn't include ExpenseType in the result set, as VFP would have no way of knowing which ExpenseType you want to show for any particular ExpenseDetail.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Thanks very much Mike(s)!

I see the wisdom of it now. Feels kind of stupid not realizing it, but I haven't really touched SQL in quite a while, and the help on SELECT is really vague in the VFP documentation.

Mike L, ExpenseType is the major grouping, and ExpensePurpose is unique among Type, so to put them together in the report output, I do actually need them grouped. And now they've asked me to differentiate between Domestic expense and Overseas expense, so my SELECT probably just became a lot more complex (or maybe 2 Selects with a variation in the WHERE clause).
Thanks!


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[ggreen61]

Try:

SELECT EXPENSETYPE, EXPENSEPURPOSE, SUM(expenseconvertedto) AS EXPACCUM FROM EXPENSEDETAIL WHERE EXPENSEID = 81 GROUP BY 1, 2

I think you have to have all the fields before the SUM() expression as part of the Group By clause. I cannot remember if you use 1, 2 notation or the field names.

 

[Chris Miller]

Scott,

I agree the help is not very helpful, but at least the VFP9 help as improved in getting more detailed about all the SQL clauses, see help topic "SELECT - SQL Command" and its list of clauses:

SELECT - SQL Command - SELECT Clause
SELECT - SQL Command - FROM Clause
SELECT - SQL Command - WITH Clause
SELECT - SQL Command - WHERE Clause
SELECT - SQL Command - GROUP BY Clause
SELECT - SQL Command - HAVING Clause
SELECT - SQL Command - UNION Clause
SELECT - SQL Command - ORDER BY Clause
SELECT - SQL Command - INTO or TO Clause
SELECT - SQL Command - Additional Display Options

And the rule is only mentioned very briefly in the first sentence:

Columns referenced in the SQL SELECT statement list, except for aggregate expressions, must be included in the GROUP BY clause.

The help makes the rule to the exception, which is quite a bad explanation. GROUP BY is all about aggregation, so the only fields you put into the SELECT clause list are the fields by which you group the result, the headers/captions of the groups. Which means like in a report these fields are common in a group of records. SQL then only differs from a report by aggregation, you only get one record per group and not all the group records, so for all other fields than the ones determining the group, you have to have an aggregation function that works on all values of these columns within the group and aggregates them, ie COUNT(), SUM(), AVG(), MIN(), MAX()... There are a few more, but MIN() and MAX() also show that "aggregation" does not necessarily mean all values of a column are aggregated, but only one is picked.

The internet can be a false friend about VFP queries with a GROUP BY clause, as earlier versions weren't that strict about the GROUP BY clause - or the SELECT fieldlist. But any other database is as strict about GROUP BY as VFP9 finally also is, so it's not just VFP.

What's a disadvantage of VFP is: You cannot group by Memo, General, or Blob fields. It could also be handy to group by memo fields in some cases, especially when it's used for short variable length texts as a real varchar alternative.

Chriss

 

[Mike Lewis]

To add to Chris's advice ....

It's worth keeping in mind that, since 9.0, VFP closely follows the ANSI-92 standard for SQL. That means that, if you want to learn the language in detail, you don't have to rely on VFP's Help. You can consult a large number of books, magazine articles and on-line tutorial on SQL in general. Much of what you read there will apply to VFP.

There are of course lots of implementation-specific details that you will need to keep in mind. Perhaps the most widely-used implementation of SQL is Microsoft's T-SQL, as found in SQL Server. This has a lot in common with VFP's SQL, but there are some details that are different and could catch you out. I've listed some of these in my article, SQL SELECT in VFP and T-SQL. (But keep in mind that I haven't updated this article since SQL Server 2005, so it will be somewhat out of date now.)

It's worth persevering. Often, a single SQL statement can do what would take several lines of code in VFP's native language, and SQL is often (but not always) more efficient.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

Tamar Granor published a range of very good conference papers on SQL. You may download them from [link ]

http://www.tomorrowssolutionsllc.com/conferencepapers.php

[/url].

hth

MarK