Showing posts with label grouped. Show all posts
Showing posts with label grouped. Show all posts

Wednesday, March 28, 2012

Please help - urgent!

I have a matrix that displays data for tags per day on each page. The
matrix
has a grouped column that does the grouping by day (this column is returned
from a stored procedure) and page breaks at the end of the day.
This is what I need - before I page break, I need to also display the
statistics per day like avg, min and max values for that day for all the
tags.
Date Tag1 Tag2 Tag3 Tag4
======================================== 10/01/2003 00:00 2 4 6 7
10/01/2003 01:00 2 4 6 7
10/01/2003 02:00 2 4 6 7
10/01/2003 03:00 2 4 6 7
......
10/01/2003 21:00 2 4 6 7
10/01/2003 22:00 2 4 6 7
10/01/2003 23:00 2 4 6 7
========================================= Daily Statistics
--
Sum: 48 96 144 168
Average: 2 4 6
7
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
======> the first one is the matrix (grouped by day and page breaks after
each day)
=======> The Daily Statistics part (summary) I am not able to do. Since the
matrix has a page break per
day, I am not able to get the statistics also on the same page for that day.
Any help will be highly appreciated. Thanks.Try putting your matrix inside a table. Your outer group goes in the
table -- with summaries in the table group footer -- and your inner group is
handled in the matrix.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"KMP" <KMP@.msn.com> wrote in message
news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
>I have a matrix that displays data for tags per day on each page. The
> matrix
> has a grouped column that does the grouping by day (this column is
> returned
> from a stored procedure) and page breaks at the end of the day.
> This is what I need - before I page break, I need to also display the
> statistics per day like avg, min and max values for that day for all the
> tags.
> Date Tag1 Tag2 Tag3 Tag4
> ========================================> 10/01/2003 00:00 2 4 6 7
> 10/01/2003 01:00 2 4 6 7
> 10/01/2003 02:00 2 4 6 7
> 10/01/2003 03:00 2 4 6 7
> ......
> 10/01/2003 21:00 2 4 6 7
> 10/01/2003 22:00 2 4 6 7
> 10/01/2003 23:00 2 4 6 7
> =========================================> Daily Statistics
> --
> Sum: 48 96 144
> 168
> Average: 2 4 6
> 7
> Min: 2 4 6
> 7
> Max: 2 4 6
> 7
> ==========================================> ======> the first one is the matrix (grouped by day and page breaks after
> each day)
> =======> The Daily Statistics part (summary) I am not able to do. Since
> the
> matrix has a page break per
> day, I am not able to get the statistics also on the same page for that
> day.
> Any help will be highly appreciated. Thanks.
>
>
>|||Thank you very much. It worked.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uyFiy2cFFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Try putting your matrix inside a table. Your outer group goes in the
> table -- with summaries in the table group footer -- and your inner group
is
> handled in the matrix.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "KMP" <KMP@.msn.com> wrote in message
> news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> >I have a matrix that displays data for tags per day on each page. The
> > matrix
> > has a grouped column that does the grouping by day (this column is
> > returned
> > from a stored procedure) and page breaks at the end of the day.
> >
> > This is what I need - before I page break, I need to also display the
> > statistics per day like avg, min and max values for that day for all the
> > tags.
> >
> > Date Tag1 Tag2 Tag3 Tag4
> > ========================================> > 10/01/2003 00:00 2 4 6 7
> > 10/01/2003 01:00 2 4 6 7
> > 10/01/2003 02:00 2 4 6 7
> > 10/01/2003 03:00 2 4 6 7
> > ......
> > 10/01/2003 21:00 2 4 6 7
> > 10/01/2003 22:00 2 4 6 7
> > 10/01/2003 23:00 2 4 6 7
> > =========================================> > Daily Statistics
> > --
> > Sum: 48 96 144
> > 168
> > Average: 2 4 6
> > 7
> > Min: 2 4 6
> > 7
> > Max: 2 4 6
> > 7
> > ==========================================> >
> > ======> the first one is the matrix (grouped by day and page breaks
after
> > each day)
> > =======> The Daily Statistics part (summary) I am not able to do. Since
> > the
> > matrix has a page break per
> > day, I am not able to get the statistics also on the same page for that
> > day.
> >
> > Any help will be highly appreciated. Thanks.
> >
> >
> >
> >
> >
>sql

Monday, February 20, 2012

PIVOT ISSUE

I have the following table structure and would like to pivot the data
below. The table was comprised of raw data grouped to find the count.
count question choice question2 choice2
-- -- -- --
--
7 are you happy no are you yes
14 are you happy yes are you yes
6 are you happy yes are you no
15 are you happy no are you no
When I Pivot
Select * from TableAbove
Pivot
(
Sum(count)
FOR Choice2 IN
('YES', 'NO')
)
I get
2 lines
Question choice yes no
-- -- -- --
are you happy yes 14 null
are you happy yes null 6
are you happy no 7 null
are you happy no null 15
Any Ideas would be appreciatedOn 30 May 2006 14:02:31 -0700, Troutbum wrote:

>I have the following table structure and would like to pivot the data
>below. The table was comprised of raw data grouped to find the count.
>count question choice question2 choice2
>-- -- -- --
> --
>7 are you happy no are you yes
>14 are you happy yes are you yes
>6 are you happy yes are you no
>15 are you happy no are you no
>When I Pivot
>Select * from TableAbove
>Pivot
> (
> Sum(count)
> FOR Choice2 IN
> ('YES', 'NO')
> )
>I get
>2 lines
>Question choice yes no
>-- -- -- --
>are you happy yes 14 null
>are you happy yes null 6
>are you happy no 7 null
>are you happy no null 15
>
>Any Ideas would be appreciated
Hi Troutbum,
I don't really understand yoour question. Is the output above what you
get or what you want? If it is what you currently get, then how do you
want it? And if it's what you want, then please explain how you get at
this output from the input - to me, a two-row output would make much
more sense.
I tried to reproduce your problem - after correcting some errors in the
query, I didn't get the result you quote above:
CREATE TABLE TableAbove
(cnt int NOT NULL,
question varchar(15),
choice char(3),
question2 varchar(15),
choice2 char(3))
go
INSERT INTO TableAbove (cnt, question, choice, question2, choice2)
SELECT 7, 'are you happy', 'no', 'are you ', 'yes'
UNION ALL
SELECT 14, 'are you happy', 'yes', 'are you ', 'yes'
UNION ALL
SELECT 6, 'are you happy', 'yes', 'are you ', 'no'
UNION ALL
SELECT 15, 'are you happy', 'no', 'are you ', 'no'
go
Select * from TableAbove
Pivot
(
Sum(cnt)
FOR choice2 IN
([yes], [no])
) AS p
go
DROP TABLE TableAbove
go
Result:
question choice question2 yes no
-- -- -- -- --
are you happy no are you 7 15
are you happy yes are you 14 6
Hugo Kornelis, SQL Server MVP