Grouping question...

Hi,

I am migrating some reports from MS Access2003 to SQL 2005 Reporting Services.

I have a dataset which contains columns for Sex, Age, Name etc... I firstly display the contents of this dataset in a table and this is fine. I also need to display a table of the breakdown of the age and sex. E.G:-

< 16yrs 16yrs-24yrs 25yrs-64yrs 65yrs-74yrs 75yrs-84yrs >=85yrs
Male x x x x x x

Female x x x x x x

Does anyone know if this is possible. I was going to use a DCount function (As found in access) but I can not find it in SRS. What is thet best way to produce this result?

Thanks in advance for your time

Peter Tewkesbury

BlueFlower Limited

Conditional aggregation can be achieved as follows:

=Sum(iif(Fields!Age.Value >= 25 AND Fields!Age.Value < 65, 1, 0))

-- Robert

sql

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vndct,vnexa,v nlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.
That value just uses getdate() so should be a constant as far as the query is concerned and so does not need to appear in the group by clause.

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vnd
ct,vnexa,vnlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.That value just uses getdate() so should be a constant as far as the query i
s concerned and so does not need to appear in the group by clause.sql

Group by is not the same as MySQL

Group by is not the same as MySQL
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Group by is not the same as MySQL

Group by is not the same as MySQL
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_desc
What values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:

> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>
|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:

>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Group by is not the same as MySQL

Group by is not the same as MySQL
I am migrating some of the query from mysql to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:

> Group by is not the same as MySQL
>
> I am migrating some of the query from mysql to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and
I
> would have to load the data into the temp file then get rest of the fields
.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it wou
ld
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that mysql fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that mysql fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:

>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)