Showing posts with label folks. Show all posts
Showing posts with label folks. Show all posts

Tuesday, March 27, 2012

Count of Counts in SQL Server

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parulthat doesn't make any sense

a count of a count will always be 1|||why is that so? how do we get a count of all those rows that appear in the inner query?|||okay, let's break this problem down into steps

could you please run the inner query all by itself and show me what you get|||Since the inner select query has a group by field, the number of rows it returned may be more than one also.|||it shows you different counts by student first name; the result looks like:
count
--
5
24
2
23
2|||ah yes, okay, then i think what you want is this --select count(*)
from (
select count(*)
from student
group by firstName
having count(*) > 1
) as counts|||Exactly, but this is giving me the following error:

No column was specified for column 1 of 'counts'.|||I got it! The inner select column needs an alias.
select count(*)
from (
select count(*) as 'cnt'
from student
group by firstName
having count(*) > 1
) as counts

Thanks so much for your help!

Wednesday, March 7, 2012

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexes
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
Bob Sullentrup
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is from
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexe
s
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
--
Bob Sullentrup> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is fr
om
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in messag
e
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>