Help on a simple (?) SQL query 
Author Message
 Help on a simple (?) SQL query

As an exmaple - I have a table with several hundred records storing when
support calls are logged, and in this example they spread over 17 days, the
date stored in dLogDate.

Is there a single SQL comand that will return 17 for this example, i.e. the
number of different dates stored? I can do it with 2 commands, grouping on
dLogDate in the first command to a temporary cursor then a CNT(*) on the
cursor for the second command but was curious to see if there was a single
command.

Many thanks

Stephen



Sun, 28 Aug 2005 19:30:48 GMT  
 Help on a simple (?) SQL query

You wrote  on Wed, 12 Mar 2003 11:30:48 +0000 (UTC):

 s> Is there a single SQL comand that will return 17 for this example, i.e.
 s> the number of different dates stored? I can do it with 2 commands,
 s> grouping on dLogDate in the first command to a temporary cursor then a
 s> CNT(*) on the cursor for the second command but was curious to see if
 s> there was a single command.

 s> Many thanks

 s> Stephen

SELECT DISTINCT dLogDate FROM yourtable WHERE something = .T. INTO CURSOR
tmp
?_tally

---
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts - Powered By VFP8



Sun, 28 Aug 2005 20:00:54 GMT  
 Help on a simple (?) SQL query
CREATE CURSOR Test (Field1 D)
INSERT INTO Test VALUES (DATE())
INSERT INTO Test VALUES (DATE() + 1)
INSERT INTO Test VALUES (DATE() + 1)
INSERT INTO Test VALUES (DATE() + 1)
INSERT INTO Test VALUES (DATE() + 1)
INSERT INTO Test VALUES (DATE() + 2)
INSERT INTO Test VALUES (DATE() + 2)

SELECT Count(Distinct Field1) ;
 FROM Test ;
 INTO ARRAY laCount

? laCount
*!* Note that when an array has one value you can treat it like a variable.

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://mdsn.microsoft.com/vfoxpro  http://foxcentral.net


Quote:
> .....they spread over 17 days, the
> date stored in dLogDate.

> Is there a single SQL comand that will return 17 for this example, i.e.
> the
> number of different dates stored?



Sun, 28 Aug 2005 21:09:00 GMT  
 Help on a simple (?) SQL query

Quote:
> As an exmaple - I have a table with several hundred records storing when
> support calls are logged, and in this example they spread over 17 days,
the
> date stored in dLogDate.

> Is there a single SQL comand that will return 17 for this example, i.e.
the
> number of different dates stored? I can do it with 2 commands, grouping on
> dLogDate in the first command to a temporary cursor then a CNT(*) on the
> cursor for the second command but was curious to see if there was a single
> command.

SELECT CNT(*) ;
    FROM table ;
    GROUP BY dLogDate

HTH
--
Andrew Howell



Sun, 28 Aug 2005 21:44:11 GMT  
 Help on a simple (?) SQL query
Hi, Eric!

 EdD> SELECT DISTINCT dLogDate FROM yourtable WHERE something = .T. INTO
 EdD> CURSOR tmp
 EdD> ?_tally

Or SELECT COUNT (DISTINCT dLogDate) AS nDays FROM yourtable INTO CURSOR
csrDays

WBR, Igor



Sun, 28 Aug 2005 20:39:04 GMT  
 Help on a simple (?) SQL query

Quote:
> SELECT CNT(*) ;
>     FROM table ;
>     GROUP BY dLogDate

SELECT dLogDate,Count(*);
FROM table;
GROUP BY dLogDate;
ORDER BY dLogDate

If you want to see, _which_ Date how often...
To have each field of the Group Clause in the
Order Clause to is more strict ANSI, and
that's the ENGINEBEHAVIOUR of VFP8.

Bye, Olaf.



Mon, 29 Aug 2005 07:01:18 GMT  
 Help on a simple (?) SQL query

Quote:
> > SELECT CNT(*) ;
> >     FROM table ;
> >     GROUP BY dLogDate

> SELECT dLogDate,Count(*);
> FROM table;
> GROUP BY dLogDate;
> ORDER BY dLogDate

> If you want to see, _which_ Date how often...
> To have each field of the Group Clause in the
> Order Clause to is more strict ANSI, and
> that's the ENGINEBEHAVIOUR of VFP8.

But he didn't..
He wanted a single SQL command to return the count of dates. Everyone else's
answer didn't quite do that.

Best regards
--
Andrew Howell



Mon, 29 Aug 2005 18:11:44 GMT  
 Help on a simple (?) SQL query
Many thanks everyone for all your suggestions

Stephen


Quote:


> > > SELECT CNT(*) ;
> > >     FROM table ;
> > >     GROUP BY dLogDate

> > SELECT dLogDate,Count(*);
> > FROM table;
> > GROUP BY dLogDate;
> > ORDER BY dLogDate

> > If you want to see, _which_ Date how often...
> > To have each field of the Group Clause in the
> > Order Clause to is more strict ANSI, and
> > that's the ENGINEBEHAVIOUR of VFP8.

> But he didn't..
> He wanted a single SQL command to return the count of dates. Everyone
else's
> answer didn't quite do that.

> Best regards
> --
> Andrew Howell



Mon, 29 Aug 2005 19:12:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Simple Search Engine SQL Query Question

2. Sql Query - One Simple Question

3. Help!! SQL Query query (simple ?)

4. [sql server] The query processor could not produce a query plan

5. Need help on SQL query

6. Help With SQL Query

7. SQL Masters - Please Help With This Query

8. SQL Query - need help.

9. Help on SQL Query (2 joins possible?)

10. SQL Query help please

11. Need help with complex SQL query

12. Sql Query Help

 

 
Powered by phpBB® Forum Software