Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Count Occurances Of Given Value

Hello All,

I have a question that has been vexing me for some time now. It keeps coming up when I'm trying to write queries for SSRS reports. Lets say I have a table that has 3 columns to keep track of people's gender in a annonomys survay (very basic example):

Month (varchar) | Year (smallint) | Gender (bool)

I want to return a dataset that is grouped by Month and Year and that contains a count of each Gender which would look something like this:

Month | Year | [Male Count] | [Female Count]

January | 2006 | 100 | 120
February | 2006 | 130 | 110
March | 2006 | 120 | 145
April | 2006 | 105 | 125

How would I acheive a dataset like this? Is it possible? Do I need to join the table to itself? If so do I use an Inner Join, an Outer Join, or a Left/Right Join? Any help would be extremely appreciated.

Thanks!

Tennyson

There are several possibilities. One option:

SELECT [Month], [Year],
COUNT(CASE WHEN Gender = 0 THEN 1 END) AS MaleCount,
COUNT(CASE WHEN Gender = 1 THEN 1 END) AS FemaleCount
FROM YourSurveyTable

GROUP BY [Month], [Year]

-Sue

|||

Thank you Sue! Your help was much appreciated.

Thanks,

Tennyson

Could not find database ID 102 ?

It happens to me that in very complex sql queries I get a message:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 102. Database may not be activated yet or may be
in transition.
The query then just doesn't run. What the hack does that mean?
Example:
The following one works:
select field1, ..., field4
from
(
select field1, ..., field4 from table1
union all
select field1, ..., field4 from table2
union all
select field1, ..., field4 from view3
)
group by field1...
but when I replace view3 with its sql then I get the mentioned error. It's
not the first time it's happened to me.
Regards,
Marcin
Hi
Looks like the one DB you are trying to access is not online.
Run
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
and it will tell you which DB it is. Check the status of the DB in EM.
Regards
Mike
"pexiak" wrote:

> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>
>
|||It does not return any records. It is impossible that that database is
offline, it is being used constantly and no problems appear.
Uytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa w
wiadomoci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...[vbcol=seagreen]
> Hi
> Looks like the one DB you are trying to access is not online.
> Run
> SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
> and it will tell you which DB it is. Check the status of the DB in EM.
> Regards
> Mike
> "pexiak" wrote:
be[vbcol=seagreen]
It's[vbcol=seagreen]
|||Can be timeout error... troubleshoot in that way
"pexiak" wrote:

> It does not return any records. It is impossible that that database is
> offline, it is being used constantly and no problems appear.
> U?ytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa3 w
> wiadomo?ci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...
> be
> It's
>
>
|||Hi,
Which service pack you are using? Looks like this a bug. Check the below KB
article.
http://support.microsoft.com/kb/834688
Thanks
Hari
SQL Server MVP
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>
|||This happened to me once when I had UDF in a view with derived tables
when I took the code from the UDF and pasted it in the SQL it was fine
I guess it was something to do with the levels of nesting and joining with
derived tables
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>

Could not find database ID 102 ?

It happens to me that in very complex sql queries I get a message:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 102. Database may not be activated yet or may be
in transition.
The query then just doesn't run. What the hack does that mean?
Example:
The following one works:
select field1, ..., field4
from
(
select field1, ..., field4 from table1
union all
select field1, ..., field4 from table2
union all
select field1, ..., field4 from view3
)
group by field1...
but when I replace view3 with its sql then I get the mentioned error. It's
not the first time it's happened to me.
Regards,
MarcinHi
Looks like the one DB you are trying to access is not online.
Run
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
and it will tell you which DB it is. Check the status of the DB in EM.
Regards
Mike
"pexiak" wrote:

> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may b
e
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>
>|||It does not return any records. It is impossible that that database is
offline, it is being used constantly and no problems appear.
Uytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa w
wiadomoci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...[vbcol=seagreen]
> Hi
> Looks like the one DB you are trying to access is not online.
> Run
> SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
> and it will tell you which DB it is. Check the status of the DB in EM.
> Regards
> Mike
> "pexiak" wrote:
>
be[vbcol=seagreen]
It's[vbcol=seagreen]|||Can be timeout error... troubleshoot in that way
"pexiak" wrote:

> It does not return any records. It is impossible that that database is
> offline, it is being used constantly and no problems appear.
> U?ytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa3 w
> wiadomo?ci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...
> be
> It's
>
>|||Hi,
Which service pack you are using? Looks like this a bug. Check the below KB
article.
http://support.microsoft.com/kb/834688
Thanks
Hari
SQL Server MVP
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>|||This happened to me once when I had UDF in a view with derived tables
when I took the code from the UDF and pasted it in the SQL it was fine
I guess it was something to do with the levels of nesting and joining with
derived tables
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>

Could not find database ID 102 ?

It happens to me that in very complex sql queries I get a message:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 102. Database may not be activated yet or may be
in transition.
The query then just doesn't run. What the hack does that mean?
Example:
The following one works:
select field1, ..., field4
from
(
select field1, ..., field4 from table1
union all
select field1, ..., field4 from table2
union all
select field1, ..., field4 from view3
)
group by field1...
but when I replace view3 with its sql then I get the mentioned error. It's
not the first time it's happened to me.
Regards,
MarcinHi
Looks like the one DB you are trying to access is not online.
Run
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
and it will tell you which DB it is. Check the status of the DB in EM.
Regards
Mike
"pexiak" wrote:
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>
>|||It does not return any records. It is impossible that that database is
offline, it is being used constantly and no problems appear.
U¿ytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa³ w
wiadomo¶ci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...
> Hi
> Looks like the one DB you are trying to access is not online.
> Run
> SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
> and it will tell you which DB it is. Check the status of the DB in EM.
> Regards
> Mike
> "pexiak" wrote:
> > It happens to me that in very complex sql queries I get a message:
> >
> > Server: Msg 913, Level 16, State 8, Line 1
> > Could not find database ID 102. Database may not be activated yet or may
be
> > in transition.
> >
> > The query then just doesn't run. What the hack does that mean?
> >
> > Example:
> > The following one works:
> >
> > select field1, ..., field4
> > from
> > (
> > select field1, ..., field4 from table1
> > union all
> > select field1, ..., field4 from table2
> > union all
> > select field1, ..., field4 from view3
> > )
> > group by field1...
> >
> > but when I replace view3 with its sql then I get the mentioned error.
It's
> > not the first time it's happened to me.
> > Regards,
> > Marcin
> >
> >
> >|||Can be timeout error... troubleshoot in that way
"pexiak" wrote:
> It does not return any records. It is impossible that that database is
> offline, it is being used constantly and no problems appear.
> U¿ytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa³ w
> wiadomo¶ci news:7478A463-F2A6-4630-81BC-41E358917FB4@.microsoft.com...
> > Hi
> >
> > Looks like the one DB you are trying to access is not online.
> >
> > Run
> > SELECT [name] FROM master.dbo.sysdatabases WHERE dbid = 102
> > and it will tell you which DB it is. Check the status of the DB in EM.
> >
> > Regards
> > Mike
> >
> > "pexiak" wrote:
> >
> > > It happens to me that in very complex sql queries I get a message:
> > >
> > > Server: Msg 913, Level 16, State 8, Line 1
> > > Could not find database ID 102. Database may not be activated yet or may
> be
> > > in transition.
> > >
> > > The query then just doesn't run. What the hack does that mean?
> > >
> > > Example:
> > > The following one works:
> > >
> > > select field1, ..., field4
> > > from
> > > (
> > > select field1, ..., field4 from table1
> > > union all
> > > select field1, ..., field4 from table2
> > > union all
> > > select field1, ..., field4 from view3
> > > )
> > > group by field1...
> > >
> > > but when I replace view3 with its sql then I get the mentioned error.
> It's
> > > not the first time it's happened to me.
> > > Regards,
> > > Marcin
> > >
> > >
> > >
>
>|||Hi,
Which service pack you are using? Looks like this a bug. Check the below KB
article.
http://support.microsoft.com/kb/834688
Thanks
Hari
SQL Server MVP
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>|||This happened to me once when I had UDF in a view with derived tables
when I took the code from the UDF and pasted it in the SQL it was fine
I guess it was something to do with the levels of nesting and joining with
derived tables
"pexiak" <mpeksa@.poczta.onet.pl> wrote in message
news:d2j4t6$a9g$1@.news.onet.pl...
> It happens to me that in very complex sql queries I get a message:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 102. Database may not be activated yet or may
> be
> in transition.
> The query then just doesn't run. What the hack does that mean?
> Example:
> The following one works:
> select field1, ..., field4
> from
> (
> select field1, ..., field4 from table1
> union all
> select field1, ..., field4 from table2
> union all
> select field1, ..., field4 from view3
> )
> group by field1...
> but when I replace view3 with its sql then I get the mentioned error. It's
> not the first time it's happened to me.
> Regards,
> Marcin
>

Could not allocate new page for database TEMPDB

I get the following error when doing a variety of basic queries on other databases:

"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.

Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%

Both tempdb data file and transaction file are on D: but all drives have ample space:
c: 25 GB free
D: 69 GB free
E: 175 GB free

sp_spaceused returns the following for tempdb:

name size unallocated space
tempdb 4.00 MB 1.45 MB

reserved data index_size unused
568 KB 176 KB 304 KB 88 KB

sp_helpdb returned for tempdb:

tempdb
4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80

This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)

Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.|||doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.

Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.

Thanks!

cost estimate

Hi, all
I am writing a midware for some heterogeneous data sources, and I need to do some cost estimation to some SQL queries before they are sent to an underlying SQL server.
My question is, is it possible to get the cost estimates of queries from SQL server by some API ? If not, can I get the numbers of distinct values for attributes from the system catalogs ? and how about other statistics, such as cardinality, selectivity?

Thanks a lot!Use QUERY EXECUTION PLAN from Query analyzer and also PROFILER to assess the query process time and indexes.|||Originally posted by Satya
Use QUERY EXECUTION PLAN from Query analyzer
Yes, I can see the execution plan graphiclly displayed in Query analyzer.
But is there a way to use that information in my application?
BTW, what is PROFILER?|||use:

set showplan_all on

it will return what I think you're looking for, in table-based format, which you can iterate through and do what you wish with.|||Thanks strader.

PROFILER is tool used to trace the events on SQL Server, which is best used to know slow running queries and process.

Refer to books online for more information.|||Originally posted by Satya
Thanks strader.

PROFILER is tool used to trace the events on SQL Server, which is best used to know slow running queries and process.

Refer to books online for more information.

Thanks a lot!