Whatz wrong in my querry

  • Thread starter Thread starter mario
  • Start date Start date
M

mario

table name: "table"
field names it has: "field1"

For all the rows in the table I want to find the no. of
occurances for each row. I tried the following sql and it
gives me error.

Select field1, [Select count(t2.field1) from table as t2
where t1.field1=t2.field1] As NoOccurance From table

Pls help
 
Thanks for your prompt response.

I did find that enclosing the table name in "[]" does the
trick. Now I am taking that querry to the next level.
When I run it, it says error in where clause.

SELECT t1.ErrorID, t1.STRUCTURE_ID, (select t2.errorID
from [GK-1-StructsWithoutMatchingPipes] as t2 where
t1.structure_id=t2.structure_id and count
(t2.structure_id)>1 ) AS DuplicatesInternal
FROM [GK-1-StructsWithoutMatchingPipes] AS t1;

Please help.

Thanks
Mario
-----Original Message-----
How about this:

SELECT field1, COUNT(*) AS NoOccurance
FROM table
GROUP BY field1

table name: "table"
field names it has: "field1"

For all the rows in the table I want to find the no. of
occurances for each row. I tried the following sql and it
gives me error.

Select field1, [Select count(t2.field1) from table as t2
where t1.field1=t2.field1] As NoOccurance From table

Pls help
Click to expand...


.
Click to expand...
 
mario said:
Thanks for your prompt response.

I did find that enclosing the table name in "[]" does the
trick. Now I am taking that querry to the next level.
When I run it, it says error in where clause.

SELECT t1.ErrorID, t1.STRUCTURE_ID, (select t2.errorID
from [GK-1-StructsWithoutMatchingPipes] as t2 where
t1.structure_id=t2.structure_id and count
(t2.structure_id)>1 ) AS DuplicatesInternal
FROM [GK-1-StructsWithoutMatchingPipes] AS t1;
Click to expand...

You insist on doing it the hard way?

count()>1 should not be in WHERE but in HAVING
 
It's not clear to me exactly what you are trying to do (a small example with
a few input records and corresponding output would help), but if you're
trying to return a list of distinct combinations of ErrorID and STRUCTURE_ID
for which there is more than one record in GK-1-StructsWithoutMatchingPipes,
you might try something like this:

SELECT
t1.ErrorID,
t1.STRUCTURE_ID
FROM
[GK-1-StructsWithoutMatchingPipes]
GROUP BY
t1.ErrorID,
t1.STRUCTURE_ID
HAVING
Count(*) > 1

mario said:
Thanks for your prompt response.

I did find that enclosing the table name in "[]" does the
trick. Now I am taking that querry to the next level.
When I run it, it says error in where clause.

SELECT t1.ErrorID, t1.STRUCTURE_ID, (select t2.errorID
from [GK-1-StructsWithoutMatchingPipes] as t2 where
t1.structure_id=t2.structure_id and count
(t2.structure_id)>1 ) AS DuplicatesInternal
FROM [GK-1-StructsWithoutMatchingPipes] AS t1;

Please help.

Thanks
Mario
-----Original Message-----
How about this:

SELECT field1, COUNT(*) AS NoOccurance
FROM table
GROUP BY field1

table name: "table"
field names it has: "field1"

For all the rows in the table I want to find the no. of
occurances for each row. I tried the following sql and it
gives me error.

Select field1, [Select count(t2.field1) from table as t2
where t1.field1=t2.field1] As NoOccurance From table

Pls help
Click to expand...


.
Click to expand...
Click to expand...
 
Following would be my example:

Lets say following is a table:

DatabaseID InventoryID
MM-1-TE-001 21301405
MM-1-TE-002 21302406
MM-1-TE-003 21302405
MM-1-TE-004 21306540
MM-1-TE-005 21302406

I want to write a querry that
1. shows all the records
2. In another column show the databaseID's if the
InvertoryID is the same.

I would like the querry result to be:


DatabaseID InventoryID DuplicateID
MM-1-TE-001 21301405 MM-1-TE-003
MM-1-TE-002 21302406 MM-1-TE-005
MM-1-TE-003 21301405 MM-1-TE-001
MM-1-TE-004 21306540
MM-1-TE-005 21302406 MM-1-TE-002

Also, being a novice SQL user, I am having a tough time
understanding the types of joins, "Group By" and "HAving"
clauses. Can anybody refer me to good resources (really
simple for ordinary minds like me.)

Thanks
Mario
-----Original Message-----
It's not clear to me exactly what you are trying to do (a small example with
a few input records and corresponding output would help), but if you're
trying to return a list of distinct combinations of ErrorID and STRUCTURE_ID
for which there is more than one record in GK-1- StructsWithoutMatchingPipes,
you might try something like this:

SELECT
t1.ErrorID,
t1.STRUCTURE_ID
FROM
[GK-1-StructsWithoutMatchingPipes]
GROUP BY
t1.ErrorID,
t1.STRUCTURE_ID
HAVING
Count(*) > 1

Thanks for your prompt response.

I did find that enclosing the table name in "[]" does the
trick. Now I am taking that querry to the next level.
When I run it, it says error in where clause.

SELECT t1.ErrorID, t1.STRUCTURE_ID, (select t2.errorID
from [GK-1-StructsWithoutMatchingPipes] as t2 where
t1.structure_id=t2.structure_id and count
(t2.structure_id)>1 ) AS DuplicatesInternal
FROM [GK-1-StructsWithoutMatchingPipes] AS t1;

Please help.

Thanks
Mario
-----Original Message-----
How about this:

SELECT field1, COUNT(*) AS NoOccurance
FROM table
GROUP BY field1


table name: "table"
field names it has: "field1"

For all the rows in the table I want to find the no. of
occurances for each row. I tried the following sql
Click to expand...
and
it
gives me error.

Select field1, [Select count(t2.field1) from table
Click to expand...
as
t2
where t1.field1=t2.field1] As NoOccurance From table

Pls help


.
Click to expand...
Click to expand...


.
Click to expand...
 
In that case, you might try a query whose SQL looks something like this:

SELECT
[Your Table].[DatabaseID],
[Your Table].[InventoryID],
[Self].[DatabaseID] AS [DuplicateID]
FROM
[Your Table]
LEFT JOIN
[Your Table] AS [Self]
ON
[Your Table].[InventoryID] = [Self].[InventoryID]

This assumes your table is named "Your Table".

For tips on using totals queries (this includes queries that use GROUP BY or
HAVING), you might start with the "Perform calculations in a query" help
topic, and in particular the "Use the query design grid to calculate a sum,
average, count, or other total" topic that it has a link to. It explains
how to build a totals query in query design view. This might be easier to
start learning with. From the View menu in query design view, you can
select SQL View command to view the equivalent SQL.

Mario said:
Following would be my example:

Lets say following is a table:

DatabaseID InventoryID
MM-1-TE-001 21301405
MM-1-TE-002 21302406
MM-1-TE-003 21302405
MM-1-TE-004 21306540
MM-1-TE-005 21302406

I want to write a querry that
1. shows all the records
2. In another column show the databaseID's if the
InvertoryID is the same.

I would like the querry result to be:


DatabaseID InventoryID DuplicateID
MM-1-TE-001 21301405 MM-1-TE-003
MM-1-TE-002 21302406 MM-1-TE-005
MM-1-TE-003 21301405 MM-1-TE-001
MM-1-TE-004 21306540
MM-1-TE-005 21302406 MM-1-TE-002

Also, being a novice SQL user, I am having a tough time
understanding the types of joins, "Group By" and "HAving"
clauses. Can anybody refer me to good resources (really
simple for ordinary minds like me.)

Thanks
Mario
-----Original Message-----
It's not clear to me exactly what you are trying to do (a small example with
a few input records and corresponding output would help), but if you're
trying to return a list of distinct combinations of ErrorID and STRUCTURE_ID
for which there is more than one record in GK-1- StructsWithoutMatchingPipes,
you might try something like this:

SELECT
t1.ErrorID,
t1.STRUCTURE_ID
FROM
[GK-1-StructsWithoutMatchingPipes]
GROUP BY
t1.ErrorID,
t1.STRUCTURE_ID
HAVING
Count(*) > 1

Thanks for your prompt response.

I did find that enclosing the table name in "[]" does the
trick. Now I am taking that querry to the next level.
When I run it, it says error in where clause.

SELECT t1.ErrorID, t1.STRUCTURE_ID, (select t2.errorID
from [GK-1-StructsWithoutMatchingPipes] as t2 where
t1.structure_id=t2.structure_id and count
(t2.structure_id)>1 ) AS DuplicatesInternal
FROM [GK-1-StructsWithoutMatchingPipes] AS t1;

Please help.

Thanks
Mario

-----Original Message-----
How about this:

SELECT field1, COUNT(*) AS NoOccurance
FROM table
GROUP BY field1

message

table name: "table"
field names it has: "field1"

For all the rows in the table I want to find the no. of
occurances for each row. I tried the following sql and
it
gives me error.

Select field1, [Select count(t2.field1) from table as
t2
where t1.field1=t2.field1] As NoOccurance From table

Pls help


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top