grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

grouping over multiple datasets

I have two datasets on a report. Both have a common field - State. I'd like to be able to have data from each dataset on a page relevant to the common state (e.g. I want data from both datasets on every page, but only data for that state). Note that I can not join the information from the two datasets, but they do share the same grouping field.
So far, I'm not having any luck getting this to work. Can anyone tell me if what I'm trying to do is possible and shed some light if it is?

You either need to combine these two data sets in the query (UNION with a filter will probably work) or use subreports. Data regions to not support multiple 1:manys.

Grouping on multiple datasets - ANY suggestions

I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:

DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92

DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92

Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:

Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190

or

Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190

Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%

I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:

= count((Fields!sale_number.Value,"DS2"))

This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.

If I try something fancier such as:

=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))

The report won't even run.

I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).

I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.

Thank you,

cmk8895Joining datasets at a report level is not currently supported. It is on the wish list for a next release. Why don't you create the combo dataset at the data source level?

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping Multiple Dimensions?

We use Excel 2007 as our front end to our AS2005 cube. Is there a way to group/display multiple dimensions under a single area? I know that you can use Display folders with measures.

Are you wanting to have a folder displayed under which attributes from multiple dimensions are displayed, or are you just wanting attributes from multiple dimensions displayed on an axis of your pivot table?

B.

|||Hi Brian,

The former. I'd like to have a folder or section where attributes from multiple dimensions can be displayed in a group. For example, let's say I have a Products dimension with a Product Line attribute and a Ship Node dimension with a Ship Node Name attribute. I'd like to have the Product Line and Ship Node attributes under a grouping or displayed in a section called "xxx" so users would just navigate to "xxx" in the Field List to find the aforementioned attributes. Does that make sense? And is that possible in Excel 2007?
|||

When you pull data from an OLAP cube directly into Excel 2007, it gives the option of generating a Pivot Table or a Pivot Table & Chart. Either way, you get that field list on the side of the window that organizes everything the way it is organized in the cube.

I am not aware of an easy way to alter this. One thing that comes to mind is possibly connecting directly to the relational data warehouse that feeds to cube (but this would require you to by-pass SSAS security). But then, everything would be jumbled.

Another would be to have an SSRS report with a single table of the elements you want and then call the report's URL with rendering instructions for either CSV or XML (or just have the report generate an XLS and open that yourself) and then generating a pivot table off the Excel data set. Still, I can't really see going into production with the SSRS solutoin.

B.

|||

Hello! I do not think it is possible.

Actually I have created named sets with crossjoin of two separate dimensions like product and customer in AS2005 and the previous version. It is possible to build them on the server, but the problem is that no client I have seen, like ProClarity Professional, will show them(and support them). These sets(or attributes from different dimensions) are not supported in any client that I know about. They will not show up in dimension tools in clients.

Since I do not now about every client on the market I can be wrong.

HTH

Thomas Ivarsson

|||Thanks for the input Brian and Thomas! I'll give it to the rest of the day to see if I can come up with anything.

Grouping common functionality in multiple stored procedures

Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:

SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:

SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);

The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).

Appreciate if someone could help,

Oops i found the solution straight after i posted. User defined functions. Never realized you could return more than one value with a function in sql server. If there is a better solution please let me know but this seems to tick all the boxes.

Edit: I have discovered that this is not going to work for me since my stored procedure produces different columns (based on values passed in) and it appears that the Multi-statement Table-Value User-Defined Function requires you to specify the structure you will be outputting.

|||

>SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

It is preferable to select just the columns you require.

>However my view has become so complicated that i had to convert itto a stored procedure called sp_Documents.
>The problem now though isthat is that i wish to do queries against the data returned but I can'tsimply say:
>SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
>The only way i can see to do it is to create a stored procedure forevery single scenario i have passing in the appropriate values asparameters
It is tempting to code complicated IF ... SELECT ... ELSE SELECT ..., however it is generally best to a code one stored procedure for each permutation as then the query engine can optimise each variation. There are some situations where serial scanning of a table is an acceptable perfomance hit and it is possible to use the COALESCE trick to search any combination of 1 to N columns for specific value. For example if table FRED has non-null columns A through D and the sp has args &A to &D and for simplicity the allowed values are non-zero integer then:
IF &A = 0 SET &A = NULL
IF &B = 0 SET &B = NULL
IF &C = 0 SET &C = NULL
IF &D = 0 SET &D = NULL
SELECT A, B, C, D FROM FRED
WHERE COALESCE(&A, A) = A
AND COALESCE(&B, B) = B AND COALESCE(&C, C) = C AND COALESCE(&D, D) = D

If say &A is the only non-zero parameter then the effect select simplifies to SELECT A, B, C, D FROM FRED WHERE &A = A, as COALESCE selects the first non-null value.

sql

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

grouping and summing

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

the query to return your desired result would look something like this...

select date, prod, class, sum(qty)

from YourTable

group by date, prod, class

thus what you are saying in this query is aggregate the qty per date, prod, class. So if any of these values are different a new record is created. Thus the same product with two diff. class values would result in two records.

HTH,

Derek

|||Thanks for your help Derek!! I was putting the sum and group opposite of what you said.|||no prob dude, take it easy.

Groupby Unions

hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bonoWhat about creating a view with the union statement?
On the view you can perform the group by on the whole result set.

Sneaky Pie|||use pubs

select U.city, count(U.city)
from
(
select city from authors
union all
select city from publishers
) U
group by u.city|||Hi HanafiH,

your solution is much better than mine, I didn't know that this could work. So I've learned something new.

Thanks for that

Sneaky Pie

Group Sum over multiple pages with IIF

I am using an IIF statement to sum a group of detail records that spans more
than 1 page. The sum looks like this:
Sum(IIf(Fields!MySwitch.Value = 'A', Fields!MyValue.Value,0))
Im doing the sum in the table1_group1 footer and it works fine if all the
data in the group remains on the same page, but once it is expanded to 2 or
more pages I get #Error. Any suggestions?
Michael CIf you can bring from a query then it is really good instead of summing two
pages rows.
Amarnath
"Michael C" wrote:
> I am using an IIF statement to sum a group of detail records that spans more
> than 1 page. The sum looks like this:
> Sum(IIf(Fields!MySwitch.Value = 'A', Fields!MyValue.Value,0))
> Im doing the sum in the table1_group1 footer and it works fine if all the
> data in the group remains on the same page, but once it is expanded to 2 or
> more pages I get #Error. Any suggestions?
> Michael C

GROUP BY with multiple columns

Hi all,
Iam havin a rather complex query and need to add another column in the
resultset. That new column is a COUNT aggregation and I need to use the GROU
P
BY clause. Below is the query that I tried. However, there is a problem with
text, ntext or image columns being in the GROUP BY clause. Is there another
way?
QUERY:
--
SELECT
p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
p.match_id, p.shop_id,
p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
big_state, m.category_name,
m.subcategory_id, COUNT(v.filter_value_id) FROM
cds_products p
JOIN
cds_matched_categories m
ON
p.match_id = m.id
JOIN
cds_small_images s
ON
p.small_image_id = s.id
JOIN
cds_big_images b
ON
p.big_image_id = b.id
JOIN
cds_product_2_filter_values v
ON
p.id = v.product_id
WHERE p.shop_id = 66
GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
p.match_id, p.shop_id,
p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
m.subcategory_id
SCHEMA:
--
CREATE TABLE [dbo].[cds_big_images] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[shop_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_matched_categories] (
[id] [int] NOT NULL ,
[shop_id] [int] NOT NULL ,
[category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[subcategory_id] [int] NULL ,
[state] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_product_2_filter_values] (
[product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[filter_value_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_products] (
[id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[infile] [int] NOT NULL ,
[description] [text] COLLATE Latin1_General_CI_AS NULL ,
[price] [money] NOT NULL ,
[link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
[match_id] [int] NOT NULL ,
[shop_id] [int] NOT NULL ,
[small_image_id] [int] NOT NULL ,
[big_image_id] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[cds_small_images] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[state] [int] NOT NULL ,
[shop_id] [int] NOT NULL
) ON [PRIMARY]
GOYou cannot group by large objects.
May be you do something like this..
Instead of grouping by the all the columns
you can join with a subquery.
instead of joining with cds_product_2_filter_values
join it with
(select product_id, count(filter_value_id) as tot_count from
cds_product_2_filter_values) as v
and then directly select tot_count.
I don't know the busniess or the type of relationship with the table.
May be you can think in these lines and try to find the count without
grouping by the text col.
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Reik" wrote:

> Hi all,
> Iam havin a rather complex query and need to add another column in the
> resultset. That new column is a COUNT aggregation and I need to use the GR
OUP
> BY clause. Below is the query that I tried. However, there is a problem wi
th
> text, ntext or image columns being in the GROUP BY clause. Is there anothe
r
> way?
> QUERY:
> --
> SELECT
> p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
> p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
> big_state, m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) FROM
> cds_products p
> JOIN
> cds_matched_categories m
> ON
> p.match_id = m.id
> JOIN
> cds_small_images s
> ON
> p.small_image_id = s.id
> JOIN
> cds_big_images b
> ON
> p.big_image_id = b.id
> JOIN
> cds_product_2_filter_values v
> ON
> p.id = v.product_id
> WHERE p.shop_id = 66
> GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
> p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
> m.subcategory_id
>
>
> SCHEMA:
> --
> CREATE TABLE [dbo].[cds_big_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_matched_categories] (
> [id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subcategory_id] [int] NULL ,
> [state] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_product_2_filter_values] (
> [product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [filter_value_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_products] (
> [id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [infile] [int] NOT NULL ,
> [description] [text] COLLATE Latin1_General_CI_AS NULL ,
> [price] [money] NOT NULL ,
> [link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
> [match_id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [small_image_id] [int] NOT NULL ,
> [big_image_id] [int] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cds_small_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>|||If you didn't mind truncating a little data in the resultset you could
CAST() the 2 text columns (description & link) to VARCHAR(8000) in the
select list and the group by clause. Do those columns both really need
to be more than 8000 characters? I'm assuming link is a URL or some
kind of reference to another product.
Has dbo.cds_products got a primary key? I assume the id column is the
primary key. If so then you could pull all the stuff from
dbo.cds_products out into a main query and join in the GROUP BY stuff in
a derived table like this (untested):
SELECT
prod.id, prod.title, prod.state, prod.infile, prod.description,
prod.price, prod.link, prod.match_id, prod.shop_id,
prod.small_image_id, prod.big_image_id,
d.small_state, d.big_state, d.category_name, d.subcategory_id,
d.filter_count
FROM dbo.cds_products as prod
INNER JOIN
(
SELECT
p.id, s.state AS small_state, b.state AS big_state,
m.category_name,
m.subcategory_id, COUNT(v.filter_value_id) as filter_count
FROM cds_products as p
INNER JOIN cds_matched_categories AS m ON p.match_id = m.id
INNER JOIN cds_small_images AS s ON p.small_image_id = s.id
INNER JOIN cds_big_images AS b ON p.big_image_id = b.id
INNER JOIN cds_product_2_filter_values as v ON p.id =
v.product_id
WHERE p.shop_id = 66
GROUP BY p.id, s.state, b.state, m.category_name,
m.subcategory_id
) AS d ON d.id = prod.id
That way you don't need to GROUP BY the text columns as the
cds_products.id column is enough to do the grouping from that table.
*mike hodgson*
http://sqlnerd.blogspot.com
Reik wrote:

>Hi all,
>Iam havin a rather complex query and need to add another column in the
>resultset. That new column is a COUNT aggregation and I need to use the GRO
UP
>BY clause. Below is the query that I tried. However, there is a problem wit
h
>text, ntext or image columns being in the GROUP BY clause. Is there another
>way?
>QUERY:
>--
>SELECT
> p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
>p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state AS small_state, b.state AS
>big_state, m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) FROM
> cds_products p
>JOIN
> cds_matched_categories m
>ON
> p.match_id = m.id
>JOIN
> cds_small_images s
>ON
> p.small_image_id = s.id
>JOIN
> cds_big_images b
>ON
> p.big_image_id = b.id
>JOIN
> cds_product_2_filter_values v
>ON
> p.id = v.product_id
>WHERE p.shop_id = 66
>GROUP BY p.id, p.title, p.state, p.infile, p.description, p.price, p.link,
>p.match_id, p.shop_id,
> p.small_image_id, p.big_image_id, s.state, b.state, m.category_name,
> m.subcategory_id
>
>
>SCHEMA:
>--
>CREATE TABLE [dbo].[cds_big_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_matched_categories] (
> [id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [category_name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [subcategory_id] [int] NULL ,
> [state] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_product_2_filter_values] (
> [product_id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [filter_value_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_products] (
> [id] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [title] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [infile] [int] NOT NULL ,
> [description] [text] COLLATE Latin1_General_CI_AS NULL ,
> [price] [money] NOT NULL ,
> [link] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
> [match_id] [int] NOT NULL ,
> [shop_id] [int] NOT NULL ,
> [small_image_id] [int] NOT NULL ,
> [big_image_id] [int] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[cds_small_images] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [path] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
> [state] [int] NOT NULL ,
> [shop_id] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>
>|||Worked excellent thanks. One more question. You were right about these two
text columns. Somehow I didnt realize a varchar column could be more than 25
5
chars. Is there a rule of thumb whever you wanna use a large varchar column
or a text column? In my example, the descriptions will likely be less than
2000 chars and the URL's in the link column might be up to 300 chars. Should
I stick with a varchar column or is there any performance/storage drawback
with that?
"Mike Hodgson" wrote:

> If you didn't mind truncating a little data in the resultset you could
> CAST() the 2 text columns (description & link) to VARCHAR(8000) in the
> select list and the group by clause. Do those columns both really need
> to be more than 8000 characters? I'm assuming link is a URL or some
> kind of reference to another product.
> Has dbo.cds_products got a primary key? I assume the id column is the
> primary key. If so then you could pull all the stuff from
> dbo.cds_products out into a main query and join in the GROUP BY stuff in
> a derived table like this (untested):
> SELECT
> prod.id, prod.title, prod.state, prod.infile, prod.description,
> prod.price, prod.link, prod.match_id, prod.shop_id,
> prod.small_image_id, prod.big_image_id,
> d.small_state, d.big_state, d.category_name, d.subcategory_id,
> d.filter_count
> FROM dbo.cds_products as prod
> INNER JOIN
> (
> SELECT
> p.id, s.state AS small_state, b.state AS big_state,
> m.category_name,
> m.subcategory_id, COUNT(v.filter_value_id) as filter_count
> FROM cds_products as p
> INNER JOIN cds_matched_categories AS m ON p.match_id = m.i
d
> INNER JOIN cds_small_images AS s ON p.small_image_id = s.i
d
> INNER JOIN cds_big_images AS b ON p.big_image_id = b.id
> INNER JOIN cds_product_2_filter_values as v ON p.id =
> v.product_id
> WHERE p.shop_id = 66
> GROUP BY p.id, s.state, b.state, m.category_name,
> m.subcategory_id
> ) AS d ON d.id = prod.id
> That way you don't need to GROUP BY the text columns as the
> cds_products.id column is enough to do the grouping from that table.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Reik wrote:
>
>|||varchar column is better than text column anytime.. and you can store to a
max of 8000 characters. But your table page size is 8 k. So, you rowsize
cannot go beyond 8k bytes. And varchar is better than text performance wise,
manageability and you can apply more functions on it :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Nice. Then I will change the datatype of these two columns to varchar. Thank
s!
"Omnibuzz" wrote:

> varchar column is better than text column anytime.. and you can store to a
> max of 8000 characters. But your table page size is 8 k. So, you rowsize
> cannot go beyond 8k bytes. And varchar is better than text performance wis
e,
> manageability and you can apply more functions on it :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Group By Clause Help

Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?

- thanks for your time.

SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:

SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;

DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50

Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50

- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?

In SQL Plus, use:

SQL> BREAK ON "DONOR TYPE"

Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?

SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;

MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5