[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Syntax Query

Posted on 2011-10-17
12
Medium Priority
?
191 Views
Last Modified: 2012-05-12
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.


0
Comment
Question by:Zack
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 6

Expert Comment

by:ianmills2002
ID: 36978794
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
 
LVL 8

Expert Comment

by:VipulKadia
ID: 36978795
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36978797
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36978805
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
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36978809
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
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36978817
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
 

Author Comment

by:Zack
ID: 36985159
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36985331
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36985339
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
 

Author Comment

by:Zack
ID: 36985427
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
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 2000 total points
ID: 36990695
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
 

Author Closing Comment

by:Zack
ID: 37005337
You were correct it was error in my vb.net script I was using to pass the sql query.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question