Another grouping question

Hi Everyone,

I've got a question and I think the answer can be a single SQL statement.
In fact, I think I might've done this before, but I can't find the SQL.

I've got a table with names and different bugs and status ids. I'd like to
get a query that totals the various statuses. For instance, the table is

Name bugid status
Jeff 12 open
Monica 13 new
Jeff 10 closed
George 27 closed
...

and I'd like the output of the query to contain the count of the various
statuses in a single row

Name new open closed
Jeff 10 2 12
Monica 1 3 17
George 12 1 14
....

I've can't seem to figure out how to aggregate all this properly. Does
anyone have an answer or some pointers?

Thanks!Hi

Try something like

SELECT Name,
SUM(CASE WHEN STATUS <> 'open' AND STATUS <> 'closed' THEN 1 ELSE 0 END) as
New,
SUM(CASE WHEN STATUS = 'open' THEN 1 ELSE 0 END) as Open,
SUM(CASE WHEN STATUS = 'closed' THEN 1 ELSE 0 END) as Closed,
FROM MyTable
GROUP BY Name

John
"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> Hi Everyone,
> I've got a question and I think the answer can be a single SQL statement.
> In fact, I think I might've done this before, but I can't find the SQL.
> I've got a table with names and different bugs and status ids. I'd like to
> get a query that totals the various statuses. For instance, the table is
> Name bugid status
> Jeff 12 open
> Monica 13 new
> Jeff 10 closed
> George 27 closed
> ...
> and I'd like the output of the query to contain the count of the various
> statuses in a single row
> Name new open closed
> Jeff 10 2 12
> Monica 1 3 17
> George 12 1 14
> ...
> I've can't seem to figure out how to aggregate all this properly. Does
> anyone have an answer or some pointers?
> Thanks!|||Assuming that you can only have one row per user per bug (posting DDL helps
clarify this), then you can do something like this:

create table #t (
UserName sysname,
BugId int,
Status char(6),
constraint PK_t primary key (UserName, BugId)
)

insert into #t
(UserName, BugId, Status)
select 'Jeff', 12, 'Open'
insert into #t
(UserName, BugId, Status)
select 'Monica', 13, 'New'
insert into #t
(UserName, BugId, Status)
select 'Monica', 26, 'New'
insert into #t
(UserName, BugId, Status)
select 'Jeff', 10, 'Closed'
insert into #t
(UserName, BugId, Status)
select 'George', 27, 'Closed'

select
UserName,
sum(case when Status = 'New' then 1 else 0 end) as 'New',
sum(case when Status = 'Open' then 1 else 0 end) as 'Open',
sum(case when Status = 'Closed' then 1 else 0 end) as 'Closed'
from
#t
group by
UserName

drop table #t

Simon

"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> Hi Everyone,
> I've got a question and I think the answer can be a single SQL statement.
> In fact, I think I might've done this before, but I can't find the SQL.
> I've got a table with names and different bugs and status ids. I'd like to
> get a query that totals the various statuses. For instance, the table is
> Name bugid status
> Jeff 12 open
> Monica 13 new
> Jeff 10 closed
> George 27 closed
> ...
> and I'd like the output of the query to contain the count of the various
> statuses in a single row
> Name new open closed
> Jeff 10 2 12
> Monica 1 3 17
> George 12 1 14
> ...
> I've can't seem to figure out how to aggregate all this properly. Does
> anyone have an answer or some pointers?
> Thanks!|||And that's a pint for Simon as well :)

John

"Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
news:99d61b89d13e536709cd0b671bf06b88@.free.teranew s.com...
> Yes! Yes! That's it! Thanks! I feel like I owe you a beer!
> -- Craig
> John Bell wrote:
> > Hi
> > Try something like
> > SELECT Name,
> > SUM(CASE WHEN STATUS <> 'open' AND STATUS <> 'closed' THEN 1 ELSE 0
END) as
> > New,
> > SUM(CASE WHEN STATUS = 'open' THEN 1 ELSE 0 END) as Open,
> > SUM(CASE WHEN STATUS = 'closed' THEN 1 ELSE 0 END) as Closed,
> > FROM MyTable
> > GROUP BY Name
> > John
> > "Craig Jennings" <cjennings@.nospam.yahoo.com> wrote in message
> > news:9e460b30162bef361d831e6b9117081e@.free.teranew s.com...
> >>Hi Everyone,
> >>
> >>I've got a question and I think the answer can be a single SQL
statement.
> >>In fact, I think I might've done this before, but I can't find the SQL.
> >>
> >>I've got a table with names and different bugs and status ids. I'd like
to
> >>get a query that totals the various statuses. For instance, the table is
> >>
> >>Name bugid status
> >>Jeff 12 open
> >>Monica 13 new
> >>Jeff 10 closed
> >>George 27 closed
> >>...
> >>
> >>and I'd like the output of the query to contain the count of the various
> >>statuses in a single row
> >>
> >>Name new open closed
> >>Jeff 10 2 12
> >>Monica 1 3 17
> >>George 12 1 14
> >>...
> >>
> >>I've can't seem to figure out how to aggregate all this properly. Does
> >>anyone have an answer or some pointers?
> >>
> >>Thanks!
> >>
> >

AND query in single column

Hi group
I have a rather peculiar question, and I really don't know how to solve
this within an SQL statement:

Given a view (v), that results in:

IDX
------
1a
1b
2a
2c
3a

I'd like to query the view with something like:
SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

ID
------
1

or in another case:
SELECT ID FROM v WHERE (X='a' OR X='c')
would give:

ID
------
1
2
3

how can this be done?

TIA
bernhard

--
www.daszeichen.ch
remove nixspam to replyBernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> I have a rather peculiar question, and I really don't know how to solve
> this within an SQL statement:
> Given a view (v), that results in:
> ID X
> ------
> 1 a
> 1 b
> 2 a
> 2 c
> 3 a
> I'd like to query the view with something like:
> SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

SELECT ID FROM v WHERE X = 'a'
INTERSECT
SELECT ID FROM v WHERE X = 'b'

Except that that syntax only works in SQL 2005. For SQL 2000, you
could do:

SELECT ID FROM v a
WHERE X = 'a'
AND EXISTS (SELECT *
FROM v b
WHERE a.ID = b.ID
AND b.X = 'b')

> ID
> ------
> 1
> or in another case:
> SELECT ID FROM v WHERE (X='a' OR X='c')
> would give:

SELECT ID FROM v WHERE X = 'a'
UNION
SELECT ID FROM v WHERE X = 'b'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Assuming the (id, x) is unique. The following gives the first result
you asked for:

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

This operation is called Relational Division and you can find other
examples and discussion about it under that name.

Your second "OR" query:

SELECT id
FROM v
WHERE x IN ('A','C')
GROUP BY id

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> SELECT id
> FROM v
> WHERE x IN ('A','B')
> GROUP BY id
> HAVING COUNT(*)=2
> This operation is called Relational Division and you can find other
> examples and discussion about it under that name.
> Your second "OR" query:
> SELECT id
> FROM v
> WHERE x IN ('A','C')
> GROUP BY id

thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

This can easily adopted to the 'or' case:

SELECT id
FROM v
WHERE x='a' OR EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

What do you think?

bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> thanks a lot for your answer. Departing from your suggestion I have come
> to the following solution (which seem to work for my purposes):
> SELECT id
> FROM v
> WHERE x='a' AND EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

This does not look good. If you have:

DX
------
1a
10b
2a
2c
3a

you will get back 1, 2 and 3. You need to correlate the subquery with
the main query. Because the subquery is not correlated to the main
query, the EXISTS is just binary flip-flop.

> This can easily adopted to the 'or' case:
> SELECT id
> FROM v
> WHERE x='a' OR EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

If there is one row with x = 'b', you will get back all id:s. Again,
this is becaues the subquery is not correlated.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
>>
>>SELECT id
>> FROM v
>> WHERE x='a' AND EXISTS
>> (SELECT id FROM v WHERE x='b')
>> GROUP BY id
>
> This does not look good. If you have:
> DX
> ------
> 1a
> 10b
> 2a
> 2c
> 3a
> you will get back 1, 2 and 3. You need to correlate the subquery with
> the main query. Because the subquery is not correlated to the main
> query, the EXISTS is just binary flip-flop.

true.. I just realised it as well... maybe I give your solution a try as
well. The thing is: this is all part of an other query, and I am pulling
the queries together via an ASP script, so they need to be simple. but
maybe may way of doing it is not simple ;-)

this

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Assuming the (id, x) is unique. The following gives the first result
> you asked for:
> SELECT id
> FROM v
> WHERE x IN ('A','B')

can this be formulated using a LIKE '%a%' statement as well?

> GROUP BY id
> HAVING COUNT(*)=2

--
www.daszeichen.ch
remove nixspam to reply|||> doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

Are you going to tell us what the error is, or just let us take a wild
guess?

It works for me if I run this code:
CREATE TABLE v (id INTEGER, x CHAR(1), PRIMARY KEY (id,x))

INSERT INTO v SELECT 1,'A' UNION SELECT 1,'B'

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

Result:

(2 row(s) affected)

id
----
1

(1 row(s) affected)

So what did you do differently? Please post some code that will
actually reproduce the problem, tell us the error message and the
version, edition and service pack you are using.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>doesn't seem to work... x IN ('a','b') provokes an SQL Server error.
>
> Are you going to tell us what the error is, or just let us take a wild
> guess?

sorry.. my fault:
I used the expression
x IN (LIKE '%a%', LIKE '%b%') which did result in an error (incorrect
syntax error)

> So what did you do differently? Please post some code that will
> actually reproduce the problem, tell us the error message and the
> version, edition and service pack you are using.

CODE:
SELECT id
FROM v
WHERE x IN (LIKE '%a%', LIKE'%b%')
GROUP BY id
HAVING COUNT(*)=2

Error:
Incorrect Syntax near the keyword 'LIKE'

SQL Server 8.0 MSDE S.Pack 3/S.Pack 3a

cheers
Bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

But are you sure you want to use LIKE here? This is not the same as
what you originally asked for because LIKE will catch any x that
*contains* the character "A" or "B", not just x = "A" or x = "B". If
that's what you want then that will probably invalidate my assumption
about the key of your table and my query won't work as I intended. You
may have to replace COUNT(*) with COUNT(DISTINCT x).

Note that the best way to get help with your problem is to post DDL (a
CREATE TABLE statement, including keys and constraints), sample data (a
few INSERT statements) and your required results. That way we won't
have to keep guessing exactly what you mean.

See: http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

no, as my ID is unique I only want records 'sharing' the same ID AND
fullfilling the AND LIKE criteria.

ID X
------
1 auvw
1 buvw
2 axyz
2 cxyz
3 aklm

your solution would result in:

ID
--
1
2
3

but I only want to get (in my AND-problem):

ID
--
1

> But are you sure you want to use LIKE here? This is not the same as
> what you originally asked for because LIKE will catch any x that
> *contains* the character "A" or "B", not just x = "A" or x = "B".

Yes. This is correct. I have formulated the problem in a more
'simplified' way. My problem is far more 'complicated'. But I have
followed your suggestion about the relaional divison, and think I've
found a way to solve the problem (In the 'real world' I need to check
for 3 criteria):

SELECT id
FROM v AS FG1
WHERE x LIKE '%a%' AND EXISTS
(SELECT *
FROM v AS FG2
WHERE FG1.id = FG2.id AND FG2.x LIKE "%b%" AND EXISTS
(SELECT * FROM v AS FG3
WHERE FG1.id = FG2.id AND FG3.id = FG1.id AND FG3.x LIKE "%c%"))

As far as I understand it (and tested it), this seems to cover my 'AND'
problem.

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

sorry.. I was too quick with my reply.. your solution works as well :-)
puhhh.. long way to learn for me.
The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
that?

--
www.daszeichen.ch
remove nixspam to reply|||> your solution would result in:

> ID
> --
> 1
> 2
> 3

I don't think you tested that out. If you try it I think you will find
my query gives the result you asked for. Anyway it looks like your
version will work too. It's just rather more typing :-)

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>your solution would result in:
>
>>ID
>>--
>>1
>>2
>>3
>
> I don't think you tested that out. If you try it I think you will find
> my query gives the result you asked for. Anyway it looks like your
> version will work too. It's just rather more typing :-)

mine was too complicated, yours is perfect for my needs :-) Thanks a
lot. Now I have to implement this into another query... but thanks: it
works!

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> David Portas wrote:
>> Maybe this is what you intended:
>>
>> SELECT id
>> FROM v
>> WHERE x LIKE '%a%' OR x LIKE '%b%'
>> GROUP BY id
>> HAVING COUNT(*)=2
>
> sorry.. I was too quick with my reply.. your solution works as well :-)
> puhhh.. long way to learn for me.
> The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
> that?

HAVING is like WHERE, but it is applied after GROUP BY. And for this
reason you can use aggregates in HAVING.

If you run

SELECT id, cnt = COUNT(*)
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
-- HAVING COUNT(*)=2

You will get:

id cnt
-- --
1 2
2 1
3 1

But you want the row with cnt = 2. Normally you do would do this with a
WHERE clause, but you cannot use COUNT(*) in a WHERE clause. Hence,
HAVING to the rescue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Analysis Services Role created via AMO has unknown problem.

Hi,

This is a wierd problem so I hope I can explain the situation clearly

I'm creating a single role via AMO for an Analysis Services database and adding domain users to the role just fine. When our 3rd party reporting tool tries to create a report an error gets generated and they bubble back the error shown below.

Now here is the wierd part...if I simply view the Role in SQL Server Managment Management Studio and click OK the problem goes away. If I click Cancel when viewing the Role the problem persists. Obviously when I click "OK" in the Role dialog some sort of save routine is going on and fixes whatever problem is present.

I'm creating the role using standard MSDN examples and everything looks in order. Any suggestions on how I can better track down what the problem is?

System.Exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) Thanks

Could you post the code you use to generate the role? I'll compare it to some code we have that is working. My first guess is there is probably something not quite right in the permissions.

Bryan

|||

To see what is different (between the role create with AMO and the role fixed by the dialog), you can do this:

- re-create the role with AMO

- connect with 'SQL Management Studio' and script the Create or Alter operation of the role (right click on the role and there should be the 'Script' option)

- run the role dialog, press 'OK'

- script again and compare with the previous script

Adrian Dumitrascu