SQL Syntax Query

Heyas,

I have the following query.

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

I would like to peform a  count on this statement this what I have but it doesn't seem to be working.

SELECT 'Counting = ' + cast(COUNT(files.*) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

What I am doing wrong here.

Thank you.


ZackGeneral IT Goto GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ianmills2002Commented:
Hi,

You have no join on the 2 tables. Without a join clause the DB will produce a cartesian product of the 2 tables. That is, If id1 has 10 rows and id2 has 20 rows, the count os rows will be 10 * 20 = 200.
0
VipulKadiaCommented:
Write as follows :
SELECT COUNT(Files.*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

0
sachinpatil10dCommented:
Try this
SELECT 'Counting = ' + cast(COUNT(*) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
     ) tmp

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

sachinpatil10dCommented:
If you need a join as per ianmills2002 suggestions

SELECT 'Counting = ' + cast(COUNT(*) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.id = id2.id and id1.[Classification] in ('Debt', 'Asset', 'Bond')
     ) tmp
0
Umar Topia.Net Full Stack DeveloperCommented:
SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
     ) tmp
0
Umar Topia.Net Full Stack DeveloperCommented:
If you want both the things (Table and Count)


SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')

select @@RowCount
0
ZackGeneral IT Goto GuyAuthor Commented:
Heya guys I keep getting syntax error below is what have tried so far.

1st Try:

SELECT 'Counting = ' + cast(COUNT(1) as varchar(10)
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try:

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(Files.*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

Keep getting syntax errors any ideas?

0
sachinpatil10dCommented:
few changes made
SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try: 

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

Open in new window

0
sachinpatil10dCommented:
1st Try:
SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try:

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data


there is nothing named Files
0
ZackGeneral IT Goto GuyAuthor Commented:
What do you mean 'there is nothing named Files' where should it be going. I tried

SELECT COUNT(Files.*) as 'Counting'

but that didn't work either.
0
sachinpatil10dCommented:
1st Try:
SELECT 'Counting = ' + cast(COUNT(1) as varchar(10))
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

2nd Try:

SELECT Files.*
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data

select @@RowCount

3rd Try:

SELECT COUNT(*) as 'Counting'
FROM (
     SELECT 'file1' as title, id2.*
     FROM [file1] id1, [file1] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
UNION
SELECT 'file2' as title, id2.*
     FROM [file2] id1, [file2] id2
     WHERE id1.[Classification] in ('Debt', 'Asset', 'Bond')
) as Data


In the above queries  Files is not there as any column/table name or alias
so SELECT COUNT(Files.*) as 'Counting' has error
SELECT COUNT(*) as 'Counting' this should work

above 3 queries are fine please have a look
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
You were correct it was error in my vb.net script I was using to pass the sql query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.