count query problem

I have a table with a list of clients, some tasks associated with each and the status of each task (open, closed).

I want to return a SINGLE row for each client, along with a COUNT of the number of "open" and "closed" tasks.

The typical count statement I am using is this:

select SiteName, Count(IssueStatus) as cnt, IssueStatus,
from IssueMaster
group by SiteName, IssueStatus

It works fine, however, it returns a row for each combination of client and status. I would like it to return "Client, Count of Open, Count of Closed".

Any ideas?It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

-PatP|||I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?

Thanks!|||Like this:

SUM (CASE WHEN status='OPEN' THEN 1 ELSE 0 END) AS open_count|||Or still using COUNT:

COUNT (CASE WHEN status='OPEN' THEN 1 END) AS open_count

Count of no. of characters

Hi,
Could anyone please let me know how many characters can we display in a single row in Crystal Report XI?
Thanks.It depends on the size of the page. Can you give us more informations on what you are trying to do

Count for each word in a field

Is it possible to create a SELECT query that will give me the count of all the words in a single field?

SELECT field1, count(field1) FROM table1 GROUP BY field1

won't do it because field1 has multiple words and I want them broken out and counted individually.

Data example:

Field1
-------
dog
dog ate my homework
cat
dog and cat
tail
dog tail
...

I want to get a count of every occurance of each word (e.g. "dog") whether in the field by itself or with other words.

Hope this makes sense.

AlNo, you would require a function that takes a text string as input, and counts the number of occurences of another string in it so that you could write:

select field1, word_count(field1,'dog')
from table1;

Depending on your DBMS's capabilities, such a function may already exist or you may be able to write one for yourself. In Oracle for example, you could certainly write one yourself and maybe one already exists in the Oracle Text tool (I don't know).|||Tony,

Thanks for the response. I forgot to mention the DB engine I'm using: MySQL.

I'm not sure I explained it well. Per your function example you're passing the word "dog" to the function. I need it to count all of the words in each field. Using my original example, the results would look like this (with an ORDER BY added):

field1 qty
-- --
dog 4
cat 3
tail 2
and 1
ate 1
my 1

It probably still requires a function to accomplish this.

Al|||Oh, I see - that's rather different. What you need is first to split out all the words into one per row like this:

word
--
dog
dog
dog
dog
cat
cat
cat
tail
tail
and
ate
my

Then of course it is easy to group and count the words. But how to split them up? If you could be sure there were no more than N words in any sentence then you could use a brute-force approach with a user-defined function like this:

select get_word(field1,1) as word from table1
union all
select get_word(field1,2) as word from table1
union all
...
union all
select get_word(field1,N) as word from table1

However, that probably isn't what you need. I don't know MySQL at all, but in Oracle you could achieve this (without the "N" words limit) by writing a function that returns a collection (like an array), and then selecting from the results of the function - a fairly complex operation.

It may be that this can't be done in MySQL using just a select statement - you may have to write a program that populates a temporary table with all the words, and then select from that.|||I'm thinking I'll create a new table that will house the individual words and after writing the "phrase" to field1, I'll parse the words and write them to the new table for future counting.

Thanks for your feedback!
Al

Count & LastNonEmpty

Is there any way to make Count work properly on a measure group where aggregation for every single measure is set to LastNonEmpty?

Basically it's a fact table that contains periodic snapshots of data & so count only makes sense within the slice based on insertion time.

Thanks!

any help please?|||What kind of count do you want in this snapshot scenario - is it a count of all objects for which there is data - could you illustrate with some examples? And are the snapshots synchronized, or could they be created at different times?|||

Here's an example:

Product Amount ImportDate

a 1 Jan 2007

b 10 Jan 2007

c 100 Jan 2007

a 2 Jan 2007

b 11 Feb 2007

b 12 Feb 2007

so in this case for Jan 2007 there's 2 products 'a' for a total amount of 3, so count =2.

for Feb 2007 there's 2 products 'b' for a total amount of 23

So basically the aggregation on the amount column is set to lastnonempty using 'import date' to determine the last child

so for the entire year 2007 for product B the amount would be equal to the amount from Feb 2007 - count =2, amount = 23.

But the count always runs across all dates & so for the set above & entire year 2007 it would return 3 for product b - not 2.

all the snapshots are done for the same set of products - they're common across all dates & it's product measures that are being aggregated.

|||Add a Named Calculation to the fact table like [LastCount] = 1, then create a corresponding measure: [LastCount], with LastNonEmpty aggregation.|||

what a great idea - and how didn't I think of that?!

Thanks!

Could not start mirroring on a single laptop with two instances of SQL Server 2005

I tried to set up mirroring on my laptop.

I have got two instances of SQL Server 2005 SP2 on my laptop (the first one is the default instance).

Checked configuration running the following scripts

SELECT type_desc, port FROM sys.tcp_endpoints;

GO

SELECT state_desc FROM sys.database_mirroring_endpoints

go

SELECT role FROM sys.database_mirroring_endpoints;

GO

SELECT 'Metadata Check';

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

GO

Everything is OK on both instances. Tried to start mirroring and I have got this error message:

TITLE: Database Properties

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://kookaburra.sydney.ssw.com.au:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

I could connect to both instances:

C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra,5022
1> exit
C:\Documents and Settings\SergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra\sydney2005,5023
1> exit

I could ping my laptop

C:\Documents and Settings\SergeiTchernykh.SSW2000>ping -a kookaburra

Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:

Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 10.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Thank you.

Problem solved.

"Database Mirror Wizard" does not show the correct error.

This error was displayed - "Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)" after executing T-SQL statement "ALTER DATABASE SET PARTNER .."

After running SQL Server Profiler I found out that the first error was: Database Northwind wasn't configured for mirroring.

Mirror database should be in RECOVERING mode.

Dropped and restored Northwind database again with NORECOVERY and mirroring started working.

|||

Hi Sergei,

How did you create the endpoints? Since you were able to connect to the sql server instances listening on ports 5022 and 5023, I suspect that you created them for T-SQL payload and not DB mirroring.

Try using something similar to this to create the endpoints:

CREATE ENDPOINT [dbm] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

Thanks,

Kaloian.

|||

Hi Kaloian,

As long as I have got 3 instances of SQL Server 2005 (later on) on my laptop (default, instance_1 and instance_2 (witness)) I manually configured endpoint for witness using statement "CREATE ENDPOINT ..." on port 5024.

But it wasn't the problem. Problem in my case was that to set up a mirror you need a mirroring database in RECOVERING mode (Ididn't configure that) but the error returned didn't specify that.

Thanks,

Sergei

sql