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!
> >>
> >

Analysis Services Cube using SQL2000 database

Hi All
I'm trying to build my first Analysis Services (2005) cube based on fact and
dimension tables on a SQL200 database on a different server.
I'm having problems setting up the data sources - I'm assuming I need two:
one for the source SQL2000 tables and one for the target SQL2005 cube. I've
done this but when it comes to deploying the cube it looks like it's
expecting the source tables on my SQL2005 data source
I think I'm missing something fundamental. Any help or suggestions most
welcome.
Thanks
PeterHello Peter,
If I understand you correctly, you have two data sources in the data
source folder in your Analysis Services project. Can you confirm that
the data for your cube is only based on the SQL2000 data source?
If this is the case, then you should delete SQL2005 data source, as you
do not need a connection to your SQL server 2005. Make sure the data
source view is only base on the SQL2000 connection.
To deploy your cube to Analysis services you only need to set up the
deployment location in the project. This most like will be already
setup for you if you are working with everything on one PC.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Analysis Services Cube Measures showing #ERR

Hi,

We have a MS OLAP Cube using 10 dimensions and 1 fact table with measures as COUNT and DISTINCT COUNT.

When we processed the cube using Analysis Services, it was done but all the measures are showing #ERR as values. The cube is about 650MB in size and all the joins between Dimension and Fact tables seem to be OK.

What could be the possible reasons of getting #ERR in measures? Do all the datatypes and length of JOIN columns between dimension and fact HAVE TO BE the same??

Please provide any inputs as this is very urgent.
Thanks
Omkarid assume its some division by 0 error

you can deal with this by replacing a Zero with a NULL in whatever field you use as a denominator. (the bottom half of division-- i hope that was the right word)

Analysis Services 2005 Cubes

Hi,

I had designed the DW database like

1. For each report one fact and some dimensions are created

2. Now i want to create a cube. while creating a cube i got some doubts. i want to know that what is the better way to create a cube

2.1 Whether i have to use one cube for one report

or

2.2 Whether i have to create only one cube and i have to create different measures

Doubts:

1. If i go to option 2.1 then it takes more memory? (because each cube stores the data in structres)

2. if i go to option 2.2 then it hits performance? (because several users access several reports using the same cube)

Can you please help me in this.

Thanks

Dinesh

If you have common structures between your reports you will probably get much better performance and memory useage from haveing multiple measures in a single cube rather than a cube per report. The indexes and caching could all be shared between the measures increasing the performance.|||Thanks.