SQL query to count number of distinct Account / Order number combinations

[AndrewMozley]

I have a table of sales orders, XSORD.dbf. Two of the fields are the customer’s account code, 'Account’ and our sales order reference, ‘Sales_Ref’. (I can also get the customer’s name from another table XCUST using the account code to look that up).

Would like to extract from this table a cursor which groups the orders with the same customer and sales reference, and provides for each a count of how many times that same combination occurs in the table XSORD.

So the result should be something like :

Customer________ Sales Ref Count

A123 Auto supplies SR0357 1
A123 Auto Supplies SR0462 2
B506 Britsh Zinc___SR9356 1
. . .

So far I have attempted code like :

SELECT ACCOUNT, Sales_Ref, Account + Sales_Ref AS Comp1 FROM XSORD GROUP BY Comp1 INTO cursor T

But this comes up with a ‘Group By' error. I also realise that I might well need a COUNT() clause as part of the SELECT statement.

As you can see I have very limited experience of SQL SELECT - Can anyone recommend a beginner’s guide?

 

[Mike Lewis]

Andrew,

If I've understood this right, you need something like this:

SELECT Account, Sales_Ref, COUNT(*) AS "Count" FROM XSORD ;
  GROUP BY Account, Sales_Ref INTO CURSOR T

The basic rule for grouping is that you can only select fields that are either (i) part of the grouping; or (ii) agregate functions, such as COUNT(). That's why you got the grouping error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You asked for a beginner's guide for SQL SELECT. There are any number of articles and tutorials available on-line. But keep in mind that VFP's version of SQL is not 100% the same as standard SQL (ANSI 92)- although it is quite close.

A good place to start might be Tamar's site, which is very much VFP-oriented. See

http://www.tomorrowssolutionsllc.com/articles.php,

and use your browsers's page search feature to find the articles that have the word SQL in their title.

You might also be interested in one of my own articles -

http://www.ml-consult.co.uk/foxst-38.htm

- which describes the differences between VFP's version SELECT and the one in Microsoft's T-SQL (which is the version found in SQL Server).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Thanks, Mike, for recommending my site.

Andrew, the item you probably will find most useful is this:

http://tomorrowssolutionsllc.com/ConferenceSessions/Learn to use SQL.PDF

Tamar