a q about counting

Hi,

New to SQL. Got some questions about it.

Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)

It seems I can't figure out a good way to do this. Any help will be
appreciated.

ThanksDE (ooff@.hotmail.com) writes:

Quote:

Originally Posted by

Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)

SELECT T2.c1, coalesce(COUNT(T1.c1), 0)
FROM T2
LEFT JOIN T1 ON T2.c1 = T1.c1
GROUP BY T2.c1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I will give you a different solution for your homework, so you will
still have to think for yourself (which should I take? and why?):

SELECT c1, COUNT(*)
FROM T1
GROUP BY c1

UNION ALL

SELECT DISTINCT c1, 0
FROM T2
WHERE NOT EXISTS (
SELECT *
FROM T1
WHERE T1.c1 = T2.c1
)
ORDER BY c1

HTH,
Gert-Jan

DE wrote:

Quote:

Originally Posted by

>
Hi,
>
New to SQL. Got some questions about it.
>
Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)
>
It seems I can't figure out a good way to do this. Any help will be
appreciated.
>
Thanks

A problem with bcp tool

I need to transfer some data from a file .txt to a SQL Server.
I use bcp tool. The problem is about a column named 'FILE', that I've
declared as:
[FILE] VARCHAR(150) NOT NULL

The command I launch is the following:
bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
password -S server

In the format_file I've declared the format of the columns, referring to the
'FILE' one as:
3 SQLCHAR 0 150 "\t" 3 [FILE]

But when I try to import the data, the system returns the error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'FILE'.

Does anybody know why?

Thank you."Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckok38$1hs$1@.atlantis.cu.mi.it...
>I need to transfer some data from a file .txt to a SQL Server.
> I use bcp tool. The problem is about a column named 'FILE', that I've
> declared as:
> [FILE] VARCHAR(150) NOT NULL
> The command I launch is the following:
> bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
> format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
> password -S server
> In the format_file I've declared the format of the columns, referring to
> the
> 'FILE' one as:
> 3 SQLCHAR 0 150 "\t" 3 [FILE]
> But when I try to import the data, the system returns the error:
> SQLState = 37000, NativeError = 156
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
> near
> the keyword 'FILE'.
> Does anybody know why?
> Thank you.

I would guess it's because FILE is a reserved keyword in MSSQL. According to
BOL, the correct column name is not required in a format file anyway, so you
could try replacing it with a non-reserved word to see what happens.

Alternatively, try using DTS, which is often a bit 'smarter' about these
things, but you should probably change your column name to a non-reserved
word anyway. I appreciate that you might have no control over that, however.

Simon|||> I would guess it's because FILE is a reserved keyword in MSSQL. According
to
> BOL, the correct column name is not required in a format file anyway, so
you
> could try replacing it with a non-reserved word to see what happens.
> Alternatively, try using DTS, which is often a bit 'smarter' about these
> things, but you should probably change your column name to a non-reserved
> word anyway. I appreciate that you might have no control over that,
however.
> Simon
I would maintain the name of the column because my application already runs
on a DB2 preexisting Database, which contains columns with this name; I
would make less change as possible...

Thank you, I will try DTS!
Bye
Fede

A problem to connect to sql server 2000

The sql server 2000 is runing at network pc named: pc1;
The ado.net is used to access the server with OLE DB .net
data provider.
The error at the OleDbError is
Message: SQL Server does not exist or access denied
NativeError: 17,
SQLState: 08001
Below is potion of the code (in C#):
OleDbConnection connection = null;
string cnString = @."Provider=SQLOLEDB; Data Source=\\pc1;"
+ "Initial Catalog = BOM; User ID = pyoung;
Password=pyoung";
try
{
connection = OleDbConnection(cnString);
connection.Open();
}
catch
{
for (int i=0; i < myException.Errors.Count; i++)
{
this.mStatusMsg = "Message: " + myException.Errors
[i].Message + "\\n" +
"Native: " + myException.Errors
[i].NativeError.ToString() + "\\n" +
"Source: " + myException.Errors[i].Source + "\\n" +
"SQL: " + myException.Errors[i].SQLState + "\\n";
}
connection.Close();
}
Where BOM is the database to use.
What did I miss to connect to the server correctly?
Thanks.
Pat
Data Source in your string is wrong.
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=useridHere;Data
Source=ServerNameHere
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||You are right. So the Data Source is only the name
instead the path.
Thank you very much.
Pat
>--Original Message--
>Data Source in your string is wrong.
>Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=useridHere;Data
>Source=ServerNameHere
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>
|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

A problem to connect to sql server 2000

The sql server 2000 is runing at network pc named: pc1;
The ado.net is used to access the server with OLE DB .net
data provider.
The error at the OleDbError is
Message: SQL Server does not exist or access denied
NativeError: 17,
SQLState: 08001
Below is potion of the code (in C#):
OleDbConnection connection = null;
string cnString = @."Provider=SQLOLEDB; Data Source=\\pc1;"
+ "Initial Catalog = BOM; User ID = pyoung;
Password=pyoung";
try
{
connection = OleDbConnection(cnString);
connection.Open();
}
catch
{
for (int i=0; i < myException.Errors.Count; i++)
{
this.mStatusMsg = "Message: " + myException.Errors
[i].Message + "\\n" +
"Native: " + myException.Errors
[i].NativeError.ToString() + "\\n" +
"Source: " + myException.Errors[i].Source + "\\n" +
"SQL: " + myException.Errors[i].SQLState + "\\n";
}
connection.Close();
}
Where BOM is the database to use.
What did I miss to connect to the server correctly?
Thanks.
PatData Source in your string is wrong.
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=useridHere;Data
Source=ServerNameHere
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||You are right. So the Data Source is only the name
instead the path.
Thank you very much.
Pat
>--Original Message--
>Data Source in your string is wrong.
>Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=useridHere;Data
>Source=ServerNameHere
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||You're welcome!
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.