Board index » delphi » Another little SQL puzzler - at least for me...

Paul Lineh

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Another little SQL puzzler - at least for me...

Hi all,

I have the following data

ID      FLT1

10      127
10      3861
10      5061
10      10157
10      11323
10      11447
10      11447
10      13487
10      13558
10      13558
10      13558

What I want is to be able to something like

select count(select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
from TSCHEDULE
where FLT1 > 1
and ID = 10)

and the above with "and ID = 10) from TSCHEDULE

(neither works)

and get 8 returned - i.e. the guy flew 8 flights in a given month. I
can just do the

select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
from TSCHEDULE
where FLT1 > 1
and ID = 10

and then do Query.RecordCount

but I was wondering are there any more elegant ways of doing this?

TIA.

Paul...

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

 
Craig Stunt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Quote
Paul Linehan wrote:

> select count(select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> from TSCHEDULE
> where FLT1 > 1
> and ID = 10)

SELECT
  COUNT(DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1)
FROM
  TSCHEDULE
WHERE
  (TSCHEDULE.FLT1 > 1 AND TSCHEDULE.ID = 10)

        HTH,

        -Craig

--
Craig Stuntz                    cstuntz@no_spam.vertexsoftware.com
----------------                -----------------------------
Delphi Developer                Vertex Systems Corporation
& Cat Wrangler                      http://www.vertexsoftware.com

John Dempse

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


How about,

select count(flt1) from tschedule where flt>1 and id=10

I've not run this, you may need to put a group by clause (group by id,
flt1)

John

Quote
> -----Original Message-----
> From:      Paul Linehan (Paul Linehan) [SMTP:Paul Linehan]
> Posted At: 29 February 2000 15:46
> Posted To: borland.public.delphi.database.sqlservers
> Conversation:      Another little SQL puzzler - at least for me...
> Subject:   Another little SQL puzzler - at least for me...

> Hi all,

> I have the following data

> ID FLT1

> 10 127
> 10 3861
> 10 5061
> 10 10157
> 10 11323
> 10 11447
> 10 11447
> 10 13487
> 10 13558
> 10 13558
> 10 13558

> What I want is to be able to something like

> select count(select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> from TSCHEDULE
> where FLT1 > 1
> and ID = 10)

> and the above with "and ID = 10) from TSCHEDULE

> (neither works)

> and get 8 returned - i.e. the guy flew 8 flights in a given month. I
> can just do the

> select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> from TSCHEDULE
> where FLT1 > 1
> and ID = 10

> and then do Query.RecordCount

> but I was wondering are there any more elegant ways of doing this?

> TIA.

> Paul...

> D4 Pro SP3 - NT 4, SP5

> Database - MS SQL Server 7.0

> Seo mo sini!

Paul Lineh

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Quote
John Dempsey <john.demp...@datelgroup.com> wrote:
> > ID    FLT1
> > 10    127
> > 10    3861
> > 10    5061
> > 10    10157
> > 10    11323
> > 10    11447
> > 10    11447
> > 10    13487
> > 10    13558
> > 10    13558
> > 10    13558
> How about,
> select count(flt1) from tschedule where flt>1 and id=10
> I've not run this, you may need to put a group by clause (group by id,
> flt1)

Tried it, but I want the answer to be 8, not 11 which is what you have
suggested returns - it has to distinct on the combination of the rows.

Thanks for your time.

Paul...

Quote
> John

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

Paul Lineh

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Quote
Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote:
> > select count(select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> > from TSCHEDULE
> > where FLT1 > 1
> > and ID = 10)
> SELECT
>   COUNT(DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1)
> FROM
>   TSCHEDULE
> WHERE
>   (TSCHEDULE.FLT1 > 1 AND TSCHEDULE.ID = 10)

Nope, doesn't work either I'm afraid.

Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'TSCHEDULE'.

Same error as I get with my own effort.

Disregard line number, I have other stuff commented out.

Quote
>    HTH,

Afraid not, but thanks anyway.

Paul...

Quote
>    -Craig

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

David Scheid

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Give this a try:

SELECT
  COUNT(DISTINCT TSCHEDULE.ID)
FROM
  TSCHEDULE
WHERE
  (TSCHEDULE.FLT1 > 1) AND (TSCHEDULE.ID = 10)

Good Luck
- David Scheidt

<Paul Linehan (Paul Linehan)> wrote in message
news:38bbe84c.330452986@forums.inprise.com...

Quote

> Hi all,

> I have the following data

> ID FLT1

> 10 127
> 10 3861
> 10 5061
> 10 10157
> 10 11323
> 10 11447
> 10 11447
> 10 13487
> 10 13558
> 10 13558
> 10 13558

> What I want is to be able to something like

> select count(select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> from TSCHEDULE
> where FLT1 > 1
> and ID = 10)

> and the above with "and ID = 10) from TSCHEDULE

> (neither works)

> and get 8 returned - i.e. the guy flew 8 flights in a given month. I
> can just do the

> select DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1
> from TSCHEDULE
> where FLT1 > 1
> and ID = 10

> and then do Query.RecordCount

> but I was wondering are there any more elegant ways of doing this?

> TIA.

> Paul...

> D4 Pro SP3 - NT 4, SP5

> Database - MS SQL Server 7.0

> Seo mo sini!

Paul Lineh

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Quote
"David Scheidt" <dsche...@hotmail.com> wrote:
> SELECT
>   COUNT(DISTINCT TSCHEDULE.ID)
> FROM
>   TSCHEDULE
> WHERE
>   (TSCHEDULE.FLT1 > 1) AND (TSCHEDULE.ID = 10)

This returns 1, as one would expect. It is when one is trying to
select a count on a distinct of two fields, then it gets confused.

Thanks for your time.

Paul...

Quote
> - David Scheidt

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

Craig Stunt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


Quote
Craig Stuntz wrote:

> SELECT
>   COUNT(DISTINCT TSCHEDULE.ID, TSCHEDULE.FLT1)
> FROM
>   TSCHEDULE
> WHERE
>   (TSCHEDULE.FLT1 > 1 AND TSCHEDULE.ID = 10)

        Just realized this is redundant.  And doesn't work.  We don't need the
TSCHEDULE.ID in the count, because it's always 10...

SELECT
  COUNT(DISTINCT TSCHEDULE.FLT1)
FROM
  TSCHEDULE
WHERE
  (TSCHEDULE.FLT1 > 1 AND TSCHEDULE.ID = 10)

        Sorry for the false alarm...

        -Craig

--
Craig Stuntz                    cstuntz@no_spam.vertexsoftware.com
----------------                -----------------------------
Delphi Developer                Vertex Systems Corporation
& Cat Wrangler                      http://www.vertexsoftware.com

Iman L Crawfo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


This works in Oracle

SELECT COUNT(*)
  FROM (SELECT DISTINCT *
          FROM MYTESTTMP)

Iman

John Dempse

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Another little SQL puzzler - at least for me...


I've just read you're answer about only expecting 8 rows returned.  Is
FLT1 a text field or something?
If it is a numeric field (which I assumed it was looking at the data)
then I would expect >1 to return 11 records.

If it is a text record, then try something like,

        select count(flt1) from tschedule where id = 10 and flt1[1] =
'1' group by id, flt1

I don't know if the problem is in the datatype of this field or not.

Quote
> -----Original Message-----
> From:      Paul Linehan (Paul Linehan) [SMTP:Paul Linehan]
> Posted At: 29 February 2000 17:52
> Posted To: borland.public.delphi.database.sqlservers
> Conversation:      Another little SQL puzzler - at least for me...
> Subject:   Re: Another little SQL puzzler - at least for me...

> John Dempsey <john.demp...@datelgroup.com> wrote:

> > > ID       FLT1

> > > 10       127
> > > 10       3861
> > > 10       5061
> > > 10       10157
> > > 10       11323
> > > 10       11447
> > > 10       11447
> > > 10       13487
> > > 10       13558
> > > 10       13558
> > > 10       13558

> > How about,

> > select count(flt1) from tschedule where flt>1 and id=10

> > I've not run this, you may need to put a group by clause (group by
> id,
> > flt1)

> Tried it, but I want the answer to be 8, not 11 which is what you have
> suggested returns - it has to distinct on the combination of the rows.

> Thanks for your time.

> Paul...

> > John

> D4 Pro SP3 - NT 4, SP5

> Database - MS SQL Server 7.0

> Seo mo sini!

Other Threads

1. Little DLL Crashes My Little App?

2. A little Application with a little Problem

3. A little SQL help please?

4. Little question of SQL?

5. Borland - A little something in Local SQL?

6. A little Problem : SQL and delphi

7. Little help with sql statement

8. SQL little puzzle

9. BDE 5.10 shows less data that SQL Server 7 (Newbie needs a clue)

10. SQL server little brother