Using Count() in a select statement?

[Redsz]

hey guys..im back with a question regarding using count() in a sql select statement.

I am selecting a week of sales information and i would like to count the total sales per day of the week. Using sum() in this manner but for the life of me i cant get count working.

* in its most basic form the sql statment is below

select count(saleshead.dsalestamp) for dow(saleshead.dsalestamp = 1) ;
from saleshead


what do i have to do differently to make this work?

thx in advance

 

[craigber]

FOR is not a valid modifier for SQL SELECT. Use WHERE.

Once fixed, your statement will select everything for Sunday, no matter which week it was on.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mkrausnick]

select count(*) from saleshead where dow(saleshead.dsalestamp) = 1


Mike Krausnick
Dublin, California

 

[craigsboyd]

Redsz,

Using an sql statement for this is not exactly what you want to do...there would be only a single row returned with a single field that contained the count...better to put it to a variable. Try either of the following:

Select SalesHead
COUNT ALL FOR DOW(SalesHead.dsalestamp) = 1 to nCount

...or...

CALCULATE cnt() FOR DOW(SalesHead.dsalestamp) = 1 TO nCount in "SalesHead"

...either of these solutions will leave you with a variable "nCount" that holds the total count you are looking for. I recommend the Calculate statement.


Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[PaulLai]

Assume saleshead.dbf contains the sales of one week only.
You should have a field which stores the sales total of a transaction, say SaleTotal.

Try this:

select cdow(dSaleStamp) as weekday, sum(SaleTotal) as DayTotal;
from saleshead;
group by weekday

The result would look like this

Weekday DayTotal
-----------------
Sunday 20000
Monday 30000
: :

Paul