Question SQL (Group By) 
Author Message
 Question SQL (Group By)

I made a SQL-command that counts how many a field appears in a table.

For example:

City            #
===========
Las Vegas   1
New York    10
...

Totally records: 11 (but there are 100 records!!! Where are the other -
empty - 89???)

The code:
SELECT CITY, COUNT( CITY) Adress."COUNT OF CITY"
FROM "C:\Data\Adress.DB" Adress
GROUP BY ARCH_GROOTTE
ORDER BY ARCH_GROOTTE

Who knows the solution to count the empty records also??? Is it
impossible???

Marcie



Wed, 18 Jun 1902 08:00:00 GMT
 Question SQL (Group By)

I'm using Delphi 5 with the standard DBE with Paradox-files, but it will
happened with dBase-files too!


Quote:


> >I made a SQL-command that counts how many a field appears in a table.

> >For example:

> >City            #
> >===========
> >Las Vegas   1
> >New York    10
> >...

> >Totally records: 11 (but there are 100 records!!! Where are the other -
> >empty - 89???)

> >The code:
> >SELECT CITY, COUNT( CITY) Adress."COUNT OF CITY"
> >FROM "C:\Data\Adress.DB" Adress
> >GROUP BY ARCH_GROOTTE
> >ORDER BY ARCH_GROOTTE

> >Who knows the solution to count the empty records also??? Is it
> >impossible???

> How are you accessing that Paradox table? With the native BDE driver
> for Paradox? With an ODBC driver? Other?

> My testing does not indicate automatic filtering out of rows with NULL
> values. The table *could* be corrupted. Or it could be a damaged
> index, such as one on your City column.

> What version of the BDE is in use there?

> (Follow-ups to the newsgroup only, please.)

> ///////////////////////////////////////////////////////////
> Steve Koterski              "No matter how cynical you get,
> Felton, CA                  it is impossible to keep up."
>                                              -- Lily Tomlin



Wed, 18 Jun 1902 08:00:00 GMT
 Question SQL (Group By)
Thanx for answering my question, but I'm afraid it's not my answer on my
question.

Your example ( SELECT State, COUNT(City) AS TheCount ) will show the field
State and the count of field City.
But I want to count also the NILL-values true in my selection.

I want this result (in a paradox database of 1000 records):
City            Count of City
Las Vegas  100
New York    500
Phoenix      300
(empty)       100    <<<<====

All selections will show only the first 3 values, but not the last one.
I think, it's not possible to count the nill-values in fields without
calculating.
The only correct method is to count the records of the total database and
count all posibilities.

Marcel
The Netherlands


Quote:


> >I'm using Delphi 5 with the standard DBE with Paradox-files, but it will
> >happened with dBase-files too!

> >> >SELECT CITY, COUNT( CITY) Adress."COUNT OF CITY"
> >> >FROM "C:\Data\Adress.DB" Adress
> >> >GROUP BY ARCH_GROOTTE
> >> >ORDER BY ARCH_GROOTTE

> I think I may have identified the source of the problem: bad SQL. You
> should not be grouping on and counting the same column. For example,
> using the sample Paradox table Customer.db, the statement below gives
> a count of zero for the group for NULL values in the State column.

>   SELECT State, COUNT(State) AS TheCount
>   FROM ":DBDEMOS:Customer.db"
>   GROUP BY State
>   ORDER BY State

> But the statement below gives the correct result.

>   SELECT State, COUNT(City) AS TheCount
>   FROM ":DBDEMOS:Customer.db"
>   GROUP BY State
>   ORDER BY State

> But you did teach me one thing in this about local SQL. Heretofore I
> had beleieved it was not possible to have a multi-word column
> correlation name. But your statement indicates this is possible by
> enclosing the column correlation name in quotation marks and prefixing
> that with the table name or table correlation name.

>   SELECT C.State, COUNT(C.City) AS C."This is the count"
>   FROM ":DBDEMOS:Customer.db" C
>   GROUP BY C.State
>   ORDER BY C.State

> I will have to update the local SQL online help to reflect this.

> ///////////////////////////////////////////////////////////
> Steve Koterski              "No matter how cynical you get,
> Felton, CA                  it is impossible to keep up."
>                                              -- Lily Tomlin



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Select Critera/Grouping Question

2. To Joe Celko, question about GROUP BY, related to "SQL for Smarties", second edition

3. SQL question: SELECT and GROUP BY in Oracle8

4. SQL QUESTION on grouping and sum/avg over a 7 day time period

5. SQL 'group by' question

6. SQL "Group By" question

7. SQL question: count groups

8. Sql GROUP BY question

9. SQL - Sum and Group by Question

10. SQL question concerning GROUP expression

11. SQL Group By Question

12. SQL Question: Help with grouping data


 
Powered by phpBB® Forum Software