sql query

[csckid]

type filename size
___________________________
.txt ab.txt 12
.txt ab.txt 12
.txt cli.txt 14
.avi clie.avi 15

I have a table like this. i want to select the files where the filename is same(given by the user), and filesize is same and it will say how many similar files are there on the table.
for eg. if I write filename=ab.txt it will return

filename count(filename) filesize
______________________________________________________
ab.txt 2 12

I tried:
SELECT filename,count(filename), filesize from loadtable where filename='hi.txt' and filesize=filesize group by filename;

it gives a message box to type the filesize........and then output is displayed

why does it gives the messagebox

 

[DCrake]

Do you have a field that holds the filesize?

 

[namliam]

I tried:
SELECT filename,count(filename), filesize from loadtable where filename='hi.txt' and filesize=filesize group by filename;

Close but no sigar You basicaly want a simple group by query like so:

SELECT   filename,  filesize, count(*)  
from loadtable 
group by filename, Filesize
Having Count(*) > 1

This will return any filename + filesize that is atleast duplicate...
You can add where as much as you need but ... this will be your base.

P.S. Welcome to AWF

 

[csckid]

thanks it worked........ there wasn't any field with filesize

 

[Nigel Cross]

Appreciate help with the following.

Trying to link a single database record to multiple Telephone numbers i.e Home Phone No and Work Phone No and Mobile Phone No.
From a table called CLIENT ADDRESSES.

Then I want it to put the corresponding ID NUMBER from the (CLIENT ADDRESSES table), into the CIN record of my (CallLog table) where incoming calls are stored, so there is a link between the tables.

Do not need to see this happen so can I put an echo off command in?
[Echoff]

Think I’ve got the first part right. I do not need to open the table, just select the appropriate records. Is there a better command for this, other than [opentable]?
[OpenTable client Addresses,,, Home Phone No = "@CID" or Work Phone No = "@CID" or Mobile Phone No = "@CID"]

Now this is the bit I’m stomped on
Getting it to put the corresponding ID NUMBER from this (CLIENT ADDRESSES table), into the CIN record of my (CallLog table)

Thank again Nigel Cross