Grouping two similar column names but different data?

Hi All,
I have a need to group a column with he same name.
I have a column called "AccountType" which has data such as :
A1
A2
A3
A4
I am using an aggrate for this column:
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
I want to also group by the actual group type. Something like:
SELECT
AccountType,
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType, AccountType
Can someone please give me a little help with this?
Thanks very much,
John.John,
Can you post an example of the expected result?
AMB
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||John:
without knowing exactly what you want, its difficult to answer.
Is this what you want:
select t.[account type], count(*)
from (
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
) t
group by t.[Account Type]
If not then try posting some sample data set and the required output and i
am sure someone will be able to help you on that.
just incase if you wanna play around and understand what the above code is
doing then use northwind and execute this query
use northwind
go
select t.lessOrMore, count(*) , sum(t.OrderCount)
from (
select orderID, count(*) as OrderCount
, case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
from [Order Details]
group by OrderID ) t
group by t.LessOrMore
Hope the above helps
Abhishek
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||My current data result is something like this:
LastName | Account Type | NumCount
Miller | Good | 20
Miller | Not Good | 5
Jones | Not Good | 37
Miller | Not Good | 9
What I would like to see is the following:
LastName | Account Type Actual Type | NumCount
Miller | Good | A1 |
20
Miller | Not Good | A2 | 5
Jones | Not Good | A3 | 37
Miller | Not Good | A4 |
9
In the first example I am grouping by LastName, [Account Type]
In the second example I need to Group by the same and addition to the Actual
Account Type.
The problem here though is that the column "AccountType" needs to be used
twice and I don't know how to handle this. Unfortunately I can not use a
unique alias for each one that can be Grouped.
John.
"Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
> John:
> without knowing exactly what you want, its difficult to answer.
> Is this what you want:
> select t.[account type], count(*)
> from (
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account
> Type]
> FROM Tbl1
> GROUP BY AccountType
> ) t
> group by t.[Account Type]
>
> If not then try posting some sample data set and the required output and i
> am sure someone will be able to help you on that.
> just incase if you wanna play around and understand what the above code is
> doing then use northwind and execute this query
> use northwind
> go
> select t.lessOrMore, count(*) , sum(t.OrderCount)
> from (
> select orderID, count(*) as OrderCount
> , case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
> from [Order Details]
> group by OrderID ) t
> group by t.LessOrMore
>
> Hope the above helps
> Abhishek
> "John" wrote:
>|||John:
It seems you dont need a second groupby.. coz you are not doing another
group by. It seems you just need and extra column. This is what is reflected
in the result set you posted (NumCount remains the same and you just need an
extra column for actual account type)
But then again you will need to be more clear in what exacly you want
is this what you want:
LastName | Account Type | Actual Type | NumCount
Miller | Good | A1 | 20
Miller | Not Good | A2 | 3
Miller | Not Good | A3 | 2
Jones | Not Good | A3 | 30
Jones | Not Good | A4 | 7
Miller | Not Good | A4 | 9
Notice that for miller not good account i have further divided into 2 actual
account type and the sum of count 3+2 = 5.
similarly for Jones it is 30+7 = 37.
If above is what you want then you can simply code it like this
SELECT Lastname
, (CASE
WHEN AccountType = 'A1'
Then 'Good'
ELSE 'Not Good'
END) AS [Account Type]
, [Account type] AS [Actual type]
, count(*) as [NumCount]
FROM Tbl1
GROUP BY LastName, AccountType
Hope the above helps. Do let me know if this is what you were looking for.
Abhishek
"John" wrote:

> My current data result is something like this:
> LastName | Account Type | NumCount
> Miller | Good | 20
> Miller | Not Good | 5
> Jones | Not Good | 37
> Miller | Not Good | 9
> What I would like to see is the following:
> LastName | Account Type Actual Type | NumCount
> Miller | Good | A1 |
> 20
> Miller | Not Good | A2 |
5
> Jones | Not Good | A3 |
37
> Miller | Not Good | A4 |
> 9
> In the first example I am grouping by LastName, [Account Type]
> In the second example I need to Group by the same and addition to the Actu
al
> Account Type.
> The problem here though is that the column "AccountType" needs to be used
> twice and I don't know how to handle this. Unfortunately I can not use a
> unique alias for each one that can be Grouped.
> John.
> "Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
> message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
>
>

GROUPING problem

I am trying to get counts of jobs accum into 4 columns by date. When I try
to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
I don't want to group by JobSize. Below is my code if someone can help me
resolve this. Thanks.
David
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
XsmallJobs = CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END,
SmallJobs = CASE
WHEN JobSize = 'S' THEN 1
ELSE 0
END,
MedJobs = CASE
WHEN JobSize = 'M' THEN 1
ELSE 0
END,
HeavyJobs = CASE
WHEN JobSize = 'H' THEN 1
ELSE 0
END
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))All that is missing is the SUM() - the missing aggregate function of
the error message - around each CASE expression:
XsmallJobs = SUM(CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END),
Roy Harvey
Beacon Falls, CT
On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
wrote:

>I am trying to get counts of jobs accum into 4 columns by date. When I try
>to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
>invalid in the select list because it is not contained in either an
>aggregate function or the GROUP BY clause."
>I don't want to group by JobSize. Below is my code if someone can help me
>resolve this. Thanks.
>David
>ALTER VIEW dbo.vw_JobSizeByDate
>AS
>SELECT ScheduledInDate,
>XsmallJobs = CASE
>WHEN JobSize = 'X' THEN 1
>ELSE 0
>END,
>SmallJobs = CASE
>WHEN JobSize = 'S' THEN 1
>ELSE 0
>END,
>MedJobs = CASE
>WHEN JobSize = 'M' THEN 1
>ELSE 0
>END,
>HeavyJobs = CASE
>WHEN JobSize = 'H' THEN 1
>ELSE 0
>END
>FROM dbo.RepairOrder
>WHERE (RepairOrderID IS NOT NULL)
>GROUP BY ScheduledInDate
>HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>|||why are you grouping at all, maybe I've missed it, but I don't see any
aggregate functions, just add the having clause as an AND to the where,
and remove the group by:
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
AND (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))|||Use the CASE expressions inside aggregate functions:
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
SUM(CASE WHEN JobSize = 'X' THEN 1 ELSE 0 END) AS "XsmallJobs",
SUM(CASE WHEN JobSize = 'S' THEN 1 ELSE 0 END) AS "SmallJobs",
SUM(CASE WHEN JobSize = 'M' THEN 1 ELSE 0 END) AS "MedJobs",
SUM(CASE WHEN JobSize = 'H' THEN 1 ELSE 0 END) AS "HeavyJobs"
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
"David" wrote:

> I am trying to get counts of jobs accum into 4 columns by date. When I tr
y
> to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' i
s
> invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
> I don't want to group by JobSize. Below is my code if someone can help me
> resolve this. Thanks.
> David
> ALTER VIEW dbo.vw_JobSizeByDate
> AS
> SELECT ScheduledInDate,
> XsmallJobs = CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END,
> SmallJobs = CASE
> WHEN JobSize = 'S' THEN 1
> ELSE 0
> END,
> MedJobs = CASE
> WHEN JobSize = 'M' THEN 1
> ELSE 0
> END,
> HeavyJobs = CASE
> WHEN JobSize = 'H' THEN 1
> ELSE 0
> END
> FROM dbo.RepairOrder
> WHERE (RepairOrderID IS NOT NULL)
> GROUP BY ScheduledInDate
> HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>
>|||Someone actually put a "vw-"prefix on your view name! They did not
know the ISO-11179 standards - unless this table deals with
Volkswagens. Also, use the portable AS syntax instead of dialect =.
Can I assume that you have more than one repair order, in spite of a
singular table name?
Your WHERE and HAVING clauses made no sense. How can a
"repair_order_id" ever be NULL? What is the definition of an
identifier? Why are you casting temporal data to strings? That would
imply your DDL is soooo screwed up that temporal data is in strings!
Try this, after you clean up the DDL.
CREATE VIEW JobsizeByDate
(xsmalljob_cnt,
smalljob_cnt,
medjob_cnt,
heavyjob_cnt)
AS
SELECT scheduledin_date,
SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
FROM RepairOrders
GROUP BY scheduledin_date;|||Perfect. That worked. Thanks.
David
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:fimi42l7diferi1jmlaa6k1anf8lvo6t1d@.
4ax.com...
> All that is missing is the SUM() - the missing aggregate function of
> the error message - around each CASE expression:
> XsmallJobs = SUM(CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END),
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
> wrote:
>|||Ah the beauty of an sql dbms...the overblown importance of columns names :P
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145658584.662773.174100@.i39g2000cwa.googlegroups.com...
> Someone actually put a "vw-"prefix on your view name! They did not
> know the ISO-11179 standards - unless this table deals with
> Volkswagens. Also, use the portable AS syntax instead of dialect =.
> Can I assume that you have more than one repair order, in spite of a
> singular table name?
> Your WHERE and HAVING clauses made no sense. How can a
> "repair_order_id" ever be NULL? What is the definition of an
> identifier? Why are you casting temporal data to strings? That would
> imply your DDL is soooo screwed up that temporal data is in strings!
> Try this, after you clean up the DDL.
> CREATE VIEW JobsizeByDate
> (xsmalljob_cnt,
> smalljob_cnt,
> medjob_cnt,
> heavyjob_cnt)
> AS
> SELECT scheduledin_date,
> SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
> FROM RepairOrders
> GROUP BY scheduledin_date;
>|||Why do you think the vague, non-standard names will make for a good
database? That they will port? That a data dictionary will appear
magically from them? That ISO is a waste of time? That 30+ years of
SE research is wrong?|||There is a certain quality to your quantity of orthodoxy.
But you have missed the mark:)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145668737.205833.119960@.j33g2000cwa.googlegroups.com...
> Why do you think the vague, non-standard names will make for a good
> database? That they will port? That a data dictionary will appear
> magically from them? That ISO is a waste of time? That 30+ years of
> SE research is wrong?
>

Grouping issue

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
======> this is the matrix (grouped by day and page breaks after each day)
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
======> The Daily Statistics part I am not able to do. Since the matrix has
a page break per
Average: 2 4 6
7 day, I am not able to get the statistics also
on the same page for that day.
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
Any help will be highly appreciated. Thanks.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
======> this is the matrix (grouped by day and page breaks after each day)
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 ======> The Daily Statistics part I am not able to do. Since the matrix
has
a page break per
Average: 2 4 6
7 day, I am not able to get the statistics also
on the same page for that day.
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
Any help will be highly appreciated. Thanks.|||Hello,
I do not have an answer but there is some idea, that could be used.
Could you e-mail me more info about your data and matrix, so I can prepare
working sample?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"KMP" <KMP@.msn.com> wrote in message
news:OaQofbcFFHA.2180@.TK2MSFTNGP10.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
> ======> this is the matrix (grouped by day and page breaks after each day)
> 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 ======> The Daily Statistics part I am not able to do. Since the
> matrix
> has
> a page break per
> Average: 2 4 6
> 7 day, I am not able to get the statistics also
> on the same page for that day.
> Min: 2 4 6
> 7
> Max: 2 4 6
> 7
> ==========================================> Any help will be highly appreciated. Thanks.
>
>|||I have been able to figure it out. Thank you very much.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OJYwI8gFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I do not have an answer but there is some idea, that could be used.
> Could you e-mail me more info about your data and matrix, so I can prepare
> working sample?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "KMP" <KMP@.msn.com> wrote in message
> news:OaQofbcFFHA.2180@.TK2MSFTNGP10.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
> > ======> this is the matrix (grouped by day and page breaks after each
day)
> > 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 ======> The Daily Statistics part I am not able to do. Since the
> > matrix
> > has
> >
> > a page break per
> > Average: 2 4 6
> > 7 day, I am not able to get the statistics
also
> >
> > on the same page for that day.
> > Min: 2 4 6
> > 7
> > Max: 2 4 6
> > 7
> > ==========================================> >
> > Any help will be highly appreciated. Thanks.
> >
> >
> >
> >
>

Grouping columns

Hi,

I was trying to retrieve some data in such a way that it 2 columns will
be merged into one, with a column in between. I am trying to do
something like this:

SELECT LastName + ", " + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName

But SQL Server does not like this syntax (though it does work with
"LastName + FirstName").

I appreciate any help.

Thanks,
AaronSQL Server uses single quotes for strings, not double quotes. Also...
you probably want to order by the first name if the last name is the
same, correct? Try:

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName, FirstName

If it is possible for there to be NULL values or empty strings in
either of the columns then you will need to account for that as well.

HTH,
-Tom.|||SELECT LastName + ", " + FirstName AS Name
FROM EmployeeTBL
ORDER BY Name

This should work.|||Use single qutes instead of double:

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY LastName|||Hmm, I didn't notice the double quotes ealier.

SELECT LastName + ', ' + FirstName AS Name
FROM EmployeeTBL
ORDER BY Name

You can always use the final column name in the ORDER BY condition.

Grouping by column alias

I'm trying to run a query and group by a calcuated column using its alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekendingThis is a multi-part message in MIME format.
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use an alias in that context. However, you can use a derived =table to do the same thing:
select
completionType,
WeekEnding,
count(*)
from
(select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completiontype, WeekEnding
order by weekending
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message =news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
I'm trying to run a query and group by a calcuated column using its =alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekending
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot use an alias in that =context. However, you can use a derived table to do the same thing:
select
completionType, WeekEnding,
count(*)from
(select =completionType, (case datepart(dw,dateCompleted) When 2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEndingFrom tblWorkQueuewhere datecompleted is not null) as =x
group by completiontype, WeekEndingorder by weekending
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Czyzewski" wrote in message news:umg0xBxlDHA.3700=@.TK2MSFTNGP11.phx.gbl...I'm trying to run a query and group by a calcuated column using its =alias.When I do, I get an error that says:Server: Msg 207, Level 16, State 3, =Line 2Invalid column name 'WeekEnding'.Here is the SQL code. =Can someone tell me what is wrong with this.select completionType, (case datepart(dw,dateCompleted) When =2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEnding --count(*)From tblWorkQueuewhere =datecompleted is not nullgroup by completiontype, WeekEndingorder by weekending

---

--=_NextPart_000_00FE_01C396EF.A792ED00--|||Jeff
Make a derived table
select completionType,WeekEnding
from
(
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completionType,WeekEnding
--order by weekending
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message
news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I'm trying to run a query and group by a calcuated column using its alias.
> When I do, I get an error that says:
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'WeekEnding'.
>
> Here is the SQL code. Can someone tell me what is wrong with this.
> select completionType,
> (case datepart(dw,dateCompleted)
> When 2 then dateAdd(dd,4,datecompleted)
> When 3 then dateAdd(dd,3,datecompleted)
> When 4 then dateAdd(dd,2,datecompleted)
> When 5 then dateAdd(dd,1,datecompleted)
> When 6 then dateAdd(dd,0,datecompleted)
> end) as WeekEnding
> --count(*)
> From tblWorkQueue
> where datecompleted is not null
> group by completiontype, WeekEnding
> order by weekending
>

grouping by a datetime column

i want to group the records in a table by day , using a datetime column.
Therefore I have to get rid of the time of that column before grouping.
What is the proper way to do that?
thnks..prefect wrote:
> i want to group the records in a table by day , using a datetime column
.
> Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>
GROUP BY
DATEPART(month, datevalue),
DATEPART(day, datevalue),
DATEPART(year, datevalue)|||SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>i want to group the records in a table by day , using a datetime column.
>Therefore I have to get rid of the time of that column before grouping.
> What is the proper way to do that?
> thnks..
>|||> GROUP BY
> DATEPART(month, datevalue),
> DATEPART(day, datevalue),
> DATEPART(year, datevalue)
FYI, on a large table, this can be a significant performance hit...
In fact, my solution is only marginally better. The best solution would
probably combine a static calendar table (see http://www.aspfaq.com/2519 for
some practical usage).|||that is what i look for.
thanks..
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||Aaron Bertrand [SQL Server MVP] wrote:
> FYI, on a large table, this can be a significant performance hit...
> In fact, my solution is only marginally better. The best solution would
> probably combine a static calendar table (see http://www.aspfaq.com/2519 f
or
> some practical usage).
>
Agreed.|||Aaron , I want to send the DateColumnName to a UDF for some processing,
then return something.
But I have a error like "DateColumnName is not in group by clause..."
My usage is as follows:
SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
ORDER BY 1
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1;
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%23n$A18HkGHA.4304@.TK2MSFTNGP03.phx.gbl...
>|||What exactly are you doing, formatting it for the client? Why don't you let
the presentation/client tier do this? What does dbo.MyUDF do, exactly, that
CONVERT() with a style option couldn't do?
Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that exist
in the SELECT list that are not constants or aggregates must also appear in
GROUP BY clause. But a slightly more efficient way would be to perform the
function against the result instead of during the aggregation:
SELECT
dt,
dbo.MyUDF(dt),
cnt
FROM
(SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
cnt = COUNT(*)
FROM [dbo].[TableName]
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
) x
ORDER BY 1;
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1|||I created a computed Column for DateColumnName
as DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
and used this computed column for grouping and parameter for MyUdf. it is
working.
But i would wanna know if there is a better way..
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Aaron , I want to send the DateColumnName to a UDF for some processing,
> then return something.
> But I have a error like "DateColumnName is not in group by clause..."
> My usage is as follows:
> SELECT
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> dbo.MyUdf( DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))),
> COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ORDER BY 1
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:eF23h$HkGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||
> What exactly are you doing, formatting it for the client? Why don't you
> let the presentation/client tier do this?
yes it should be this way. But for some reason off my hand , i am not able
to do
in the presentation layer.

> What does dbo.MyUDF do, exactly, that CONVERT() with a style option
> couldn't do?
no, unfortunately..

> Anyway, I don't see dbo.MyUDF() in the group by clause. Columns that
> exist in the SELECT list that are not constants or aggregates must also
> appear in GROUP BY clause. But a slightly more efficient way would be to
> perform the function against the result instead of during the aggregation:
>
> SELECT
> dt,
> dbo.MyUDF(dt),
> cnt
> FROM
> (SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName])),
> cnt = COUNT(*)
> FROM [dbo].[TableName]
> GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateColumnName]))
> ) x
> ORDER BY 1;
i will try that, can you comment my other post?

> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:OPU84WIkGHA.4660@.TK2MSFTNGP05.phx.gbl...
>

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.

grouping and showing concatenated varchar column?

When you group and wants to show the aggregate of a numeric column, you do
SUM() on it.
Is there a way to do this for a varchar type so that all the values are conc
atenated
and separated by a comma for example?
And it needs to be a single select statement. Is this possible?
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com> Is there a way to do this for a varchar type so that all the values are
> concatenated and separated by a comma for example?
http://www.aspfaq.com/2529|||Thanks but none of those will work for me. It's a shame that SQL standard
doesn't have a aggregate function for something like this.
It's required often enough and it would probably be so simple to do.
If we can,
SELECT PRODUCTNAME, SUM(PRICE)
FROM SALESPRODUCT
GROUP BY PRODUCTNAME
why can't we have,
SELECT PRODUCTFAMILY, CONCAT(PRODUCTNAME, ',')
FROM SALESPRODUCT
GROUP BY PRODUCTFAMILY
I mean is that so hard?

> http://www.aspfaq.com/2529
>|||> Thanks but none of those will work for me.
Can you be more specific?

> I mean is that so hard?
The SQL Server team will have to answer that.
A question for you: Is it so hard to do this outside the database? The
result is being used outside of the database, isn't it?|||>> I mean is that so hard?
Unlike summation, concatenation requires some specific order of the
constituent items to form the csv list. Since any subset of rows in a table
are sets, they do not have any inherent order associated with it. So asking
the DBMS to provide you with an ordered list when no order exists, is
meaningless.
The workarounds involve using SQL a ordered resultset & then concatenting
the values. Some of them can be found at:
http://groups.google.com/group/micr...3e?dmode=source
With SQL 2005 you will have some more options in generating such lists,
though in most cases as Aaron mentioned, retrieving the resultset to the
client and formatting it there might be a better option.
Anith|||Ummm... wrong poster!
Anith|||I am programming against a third party OLE DB Provider such that:
- I cannot create a UDF - which would be easier.
- No Case statements
- No Declares nor multiple statements
Basically it needs to be a standard ANSI SQL and a single statement.
It's not hard to do it outside the db. And I've already done it in the pres
entation
layer. But it is more lines of code doing what seems to be a mundane task.

> Can you be more specific?
>
> The SQL Server team will have to answer that.
> A question for you: Is it so hard to do this outside the database?
> The result is being used outside of the database, isn't it?
>|||Thanks for the link. I've seen some of that. Unfortunately I'm still on
SQL 2000.
I don't think I understand your statement regarding concatenation requiring
a specific order. Why is that?
I said nothing about the order of the result set. In fact, even if it came
in no particular order, it would be ok.
But even if I needed them in a certain order, once I get a single recordset
that contain this concatenated column, it'd be a few lines of coding that
can sort the particular column in the recordset. vs. having to parse out
the rows to concatenate everything and sorting it.

> Unlike summation, concatenation requires some specific order of the
> constituent items to form the csv list. Since any subset of rows in a
> table are sets, they do not have any inherent order associated with
> it. So asking the DBMS to provide you with an ordered list when no
> order exists, is meaningless.
> The workarounds involve using SQL a ordered resultset & then
> concatenting
> the values. Some of them can be found at:
> http://groups.google.com/group/micr...er.programming/
> msg/2d85bf366dd9e73e?dmode=source
> With SQL 2005 you will have some more options in generating such
> lists, though in most cases as Aaron mentioned, retrieving the
> resultset to the client and formatting it there might be a better
> option.
>|||> Basically it needs to be a standard ANSI SQL and a single statement.
If you can't create a UDF then I'm afraid you're out of luck. This is like
saying you need a car, and you know that cars contain many parts, but you
want a car made of only a single part. Not going to happen.

> It's not hard to do it outside the db. And I've already done it in the
> presentation layer. But it is more lines of code doing what seems to be a
> mundane task.
Yep. Driving to work every morning is a mundane task too. Can't wait until
the producers of Star Trek reveal their patent-protected "beam-me-up"
technology. Until then, if I want to get to work, I still have to use the
old fashioned automobile.|||> I said nothing about the order of the result set. In fact, even if it
> came in no particular order, it would be ok.
> But even if I needed them in a certain order, once I get a single
> recordset that contain this concatenated column, it'd be a few lines of
> coding that can sort the particular column in the recordset. vs. having to
> parse out the rows to concatenate everything and sorting it.
I'm . There is ordering of the result, e.g. if you have:
1 aaron,bob,frank
2 tommy,frank,george
3 frank,bob,aaron
You'd want them listed alphabetically based on the first member in each set,
e.g.
1 aaron,bob,frank
3 frank,bob,aaron
2 tommy,frank,george
What I believe Anith is talking about is ordering of each "column", e.g. an
ordered concatenation would produce this slightly different set:
1 aaron,bob,frank
2 frank,george,tommy
3 aaron,bob,frank
Which cannot be guaranteed by SQL Server, and even when it does work, it
will have two side effects (which may or may not be desirable):
(a) it will create "order doesn't matter" duplicates (1 and 3 are now the
same)
(b) it will change the alphabetical ordering, now 1 or 3 could be first...sql

Grouping and Average Question

Hi,
My problem is as follows:
I would like to take the average value of count column grouping by drive
letter and date.
Sample table
DriveLetter Date Count
K: 2005-07-05 06:00:00:000 33.555
K: 2005-07-05 06:30:00:000 35.555
K: 2005-07-05 07:00:00:000 48.555
K: 2005-07-05 07:30:00:000 52.555
h: 2005-07-05 06:00:00:000 33.555
h: 2005-07-05 06:30:00:000 35.555
h: 2005-07-05 07:00:00:000 48.555
h: 2005-07-05 07:30:00:000 52.555
i: 2005-07-05 06:00:00:000 33.555
i: 2005-07-05 06:30:00:000 35.555
i: 2005-07-05 07:00:00:000 48.555
i: 2005-07-05 07:30:00:000 52.555
Thanks
MikeDragon9994 wrote:

> I would like to take the average value of count column grouping by
> drive letter and date.
> Sample table
> DriveLetter Date Count
> K: 2005-07-05 06:00:00:000 33.555
> K: 2005-07-05 06:30:00:000 35.555
> K: 2005-07-05 07:00:00:000 48.555
> K: 2005-07-05 07:30:00:000 52.555
select DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME) as
[Date], avg([Count]) as AvgCount
from SampleTable
group by DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME)
HTH,
Stijn Verrept.|||>> .. average value of count column grouping by drive
letter and date. <<
DATE and COUNT are reserved words in SQL.
SELECT drive_letter, foobar_date, AVG(foobar_count)
FROM Foobar
GROUP BY drive_letter, foobar_date;
I have no Stijn wants to CAST() temporal data into strings. I also
have no idea why he is also using CONVERT() unless he likes proprietary
code.|||--CELKO-- wrote:

> DATE and COUNT are reserved words in SQL.
Very true that date and count reserved words are, it's indeed better
not to use them, that's also why I put them between brackets.

> I have no Stijn wants to CAST() temporal data into strings. I also
> have no idea why he is also using CONVERT() unless he likes
> proprietary code.
Well if you check the original message you'll see that his sample date
column also contains hours and he wanted to group by date.
It maybe is a little confusing since the OP also uses Date as the
column name so it's not sure if he wants to sort by date or by the
column date (which also contains the time). HOWEVER, if you look at
the sample data you'll see that every hour only occurs once per drive
letter so it would be useless to get an average value, that why we can
be pretty sure that the OP means date (in date without time).
That's why your query is pretty much useless. If you run that query on
the data he has supplied you'll get exactly that same data back.
The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
of the time and only look at the date.
Hope this clears things up,
Stijn Verrept.|||Thanks for your help. It is what I needed to do.
Sorry for the confussion on the Date Column.
Mike
"Stijn Verrept" wrote:

> --CELKO-- wrote:
>
> Very true that date and count reserved words are, it's indeed better
> not to use them, that's also why I put them between brackets.
>
>
> Well if you check the original message you'll see that his sample date
> column also contains hours and he wanted to group by date.
> It maybe is a little confusing since the OP also uses Date as the
> column name so it's not sure if he wants to sort by date or by the
> column date (which also contains the time). HOWEVER, if you look at
> the sample data you'll see that every hour only occurs once per drive
> letter so it would be useless to get an average value, that why we can
> be pretty sure that the OP means date (in date without time).
> That's why your query is pretty much useless. If you run that query on
> the data he has supplied you'll get exactly that same data back.
> The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
> of the time and only look at the date.
> --
> Hope this clears things up,
> Stijn Verrept.
>

Grouping 2 columns into 1!

HI,
i have 2 columns named firstname and lastname, i need to get them into 1 column named name with a space between them.
Does anyone have a tip to do this?
WimmoHi Wimmo,

just something like

select column_firstname + ' ' + column_lastname
from table

The "+" concatenates two CHAR or VARCHAR columns.

Carsten|||Originally posted by CarstenK
Hi Wimmo,

just something like

select column_firstname + ' ' + column_lastname
from table

The "+" concatenates two CHAR or VARCHAR columns.

Carsten

Thanx alot that did it

Greetz Wimmo

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group Test Field

When adding a text box in my group section, the textbox width is limited to the width of the first column in the detail of the report, or any detail column. It cannot span multiple columns, even though it is the only textbox on the entire 8 inch line, it is limited to my .5 inch column 1 width.
How do I expand my group by text box to use some of that empty space ?
Thanks in advance,
ChrisFigured it out, right-click on the cell select Merge Cells and can spread across entire row. Nice!
I'm a huge fan!
Chris
"Chris" wrote:
> When adding a text box in my group section, the textbox width is limited to the width of the first column in the detail of the report, or any detail column. It cannot span multiple columns, even though it is the only textbox on the entire 8 inch line, it is limited to my .5 inch column 1 width.
> How do I expand my group by text box to use some of that empty space ?
> Thanks in advance,
> Chris

group results into a string

how to group results from a select statement with single column into a comma
separated string? e.g.
results
--
a
b
c
...
string:
a, b, c, ...nono
I suggest you doing such operations on the client side
create table #test
(
col char(1)
)
insert into #test values ('a')
insert into #test values ('b')
insert into #test values ('c')
declare @.str varchar(20)
set @.str=''
select @.str=@.str+coalesce(col,'')+';' from #test
select @.str
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> how to group results from a select statement with single column into a
comma
> separated string? e.g.
> results
> --
> a
> b
> c
> ...
> string:
> a, b, c, ...|||how can I do that in SQL Server?
"Uri Dimant" wrote:

> nono
> I suggest you doing such operations on the client side
> create table #test
> (
> col char(1)
> )
> insert into #test values ('a')
> insert into #test values ('b')
> insert into #test values ('c')
> declare @.str varchar(20)
> set @.str=''
> select @.str=@.str+coalesce(col,'')+';' from #test
> select @.str
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:BFA2B825-A041-4775-9692-1D1C5E0490AE@.microsoft.com...
> comma
>
>|||I did show you , did not I?
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
> how can I do that in SQL Server?
> "Uri Dimant" wrote:
>|||Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
used for?
"Uri Dimant" wrote:

> I did show you , did not I?
>
> "nonno" <nonno@.discussions.microsoft.com> wrote in message
> news:C9E42970-F176-4B03-ADA2-7ABFCEC1B944@.microsoft.com...
>
>|||BOL says:
COALESCE
Returns the first nonnull expression among its arguments
Fore more details please refer to the BOL.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:DB38C965-1514-4560-97ED-0C36AB7DC2F8@.microsoft.com...
> Thx Uri! It's really fantastic! Can u tell me what's the coalesce function
> used for?
> "Uri Dimant" wrote:
>
into a|||> Thx Uri! It's really fantastic!
Note that this method is also not supported, and does not guarantee correct
results in all possible cases.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Uri,
Would you mind mentioning the potential drawbacks of this method while
suggesting it, at least in the future? Several newbies with limited SQL
exposure tend to misunderstand such constructs as a valid SQL queries and
may even pass onto others as a recommended approach.
Anith|||Anith
If you read my post carefully I did mention what I would have done in such
situations

>I suggest you doing such operations on the client side
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23lPkKPqPFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Uri,
>
> Would you mind mentioning the potential drawbacks of this method while
> suggesting it, at least in the future? Several newbies with limited SQL
> exposure tend to misunderstand such constructs as a valid SQL queries and
> may even pass onto others as a recommended approach.
> --
> Anith
>|||>> If you read my post carefully I did mention what I would have done in
Ah..I did not notice that, my apologies :-(
Anith

Group members of a derived dimension

Hi, Have created a dimension based on a column in the FACT, called Age as given in the post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199100&SiteID=1) . Now, I need to group these members in custom buckets. The 'aggregate*' property is not allowing for custom buckets. Since this is a derived dimension, it is not appearing in the list of dimensions under the 'Create Custom Member formula' under 'Add BI' feature. How to accomplish this?

Secondly, If I try to create a calculated member on this dimension, why should it be attached under an existing attribute? So, even this route didnt work.

Thanks ina dvance,

If you followed the steps in that post, what you created in the fact table with your case statement is a set of derived surrogate keys. You would then link these keys to a dimension created from a table/view in your dsv. In this dimension table you could then add an extra column for a grouping and specify the group for each member.

Group ID and ID Column

Hi,

I want to get the value from a row that contains the minimum value of a field AND for which I group by a 3rd field. For example:

The table tbl1 has:

X Quantity Location

1 20 slot 1

1 34 slot 1

3 17 slot 1

42 12 slot 5

5 65 slot 5

If I just want the quantities and location I can do:

Select Location, Min(Quantity) from tbl1 group by Location

However if I want X (or any other field in the table) I cannot do:

Select X, Location, Min(Quantity) from tbl1 group by Location

because X is not in the group by clause.

And putting X in the group by clause causes incorrect results.

Does anyone know the correct select statement?

jerry

It might help if you provided sample output from the query you are trying to write.|||

In the case of a group by, any non grouped columns in the select must be contained in an aggregate function.

Think about your example, If you group by Location which value of x should you get for location = 'slot 1' 1 or 3.

so, use MIN(X) or MAX(X) in your select and it should work

|||

Can you explain what you are trying to accomplish?

;with cte

as

(

select X, Quantity, Location, row_number() over(partition by Location order by Quantity) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

If you use sql server 2000,

Code Snippet

Create Table #qty (

[X] Varchar(100) ,

[Quantity] Varchar(100) ,

[Location] Varchar(100)

);

Insert Into #qty Values('1','20','slot1');

Insert Into #qty Values('1','34','slot1');

Insert Into #qty Values('3','17','slot1');

Insert Into #qty Values('42','12','slot5');

Insert Into #qty Values('5','65','slot5');

Select * From #qty [Main]

Join (

Select

[Location]

,Min([Quantity])[Quantity]

From

#qty

Group By

[Location]

) as [Data] On [Data].[Quantity] = [Main].[Quantity]

And [Data].[Location] = [Main].[Location]

Group column name problem

How would you do this statement:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Description,Price,PurchaseQty,TotalPrice
The problem is I get an "Invalid Column Name" for Description and
TotalPrice.
I assume that is because the names are assigned.
How can I make this work?
Thanks,
Tomgroup by the expression, e.g.
...group by Case when ProductTypeID = 1 then j.ItemName when
ProductTypeID = 2 then
r.ItemName end,
Price, PurchaseQty,
Price * PurchaseQty
tshad wrote:

>How would you do this statement:
>Select rank=count(*),
> Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
>r.ItemName end as Description,
> Price, PurchaseQty, TotalPrice = Price * PurchaseQty
>from PurchaseDetail pd
>join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
>left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
>left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
>where CompanyID = 153973
>group by Description,Price,PurchaseQty,TotalPrice
>The problem is I get an "Invalid Column Name" for Description and
>TotalPrice.
>I assume that is because the names are assigned.
>How can I make this work?
>Thanks,
>Tom
>
>|||"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> group by the expression, e.g.
> ...group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID
> = 2 then
> r.ItemName end,
> Price, PurchaseQty,
> Price * PurchaseQty
>
I was hoping I wouldn't have to do that. That would mean in my larger
scripts that use large Case statements would also have to be put in the
Group by clause.
Thanks,
Tom
> tshad wrote:
>|||On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:

>"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
>news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
>I was hoping I wouldn't have to do that. That would mean in my larger
>scripts that use large Case statements would also have to be put in the
>Group by clause.
Hi Tom,
There are two workarounds:
1. Instead of including the expression in the GROUP BY, include ALL
columns used in the expression. I've never checked if ANSI standard
allows it, but AFAIK, SQL Server does.
2. Use a derived table:
SELECT result, MAX(something else)
FROM (SELECT complicated expression AS result,
something else
FROM some tables
WHERE whatever you want) AS Der
GROUP BY result
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p5g2m1pigre1nfievlll39mfp7nh9jdd94@.
4ax.com...
> On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:
>
ProductTypeID
> Hi Tom,
> There are two workarounds:
> 1. Instead of including the expression in the GROUP BY, include ALL
> columns used in the expression. I've never checked if ANSI standard
> allows it, but AFAIK, SQL Server does.
Haven't tried that yet, but wouldn't the grouping be incorrect as you are
looking at the value of the column instead of the derived value of that
expression from the column?
Not really sure why you can't use the assigned title of the column( x as
column).

> 2. Use a derived table:
> SELECT result, MAX(something else)
> FROM (SELECT complicated expression AS result,
> something else
> FROM some tables
> WHERE whatever you want) AS Der
> GROUP BY result
That was also what Peter suggested, which worked in this example.
I also looked at setting up views as we did before in my other problem, but
it seemed like overkill in this problem. I am just trying figure out at
what point I would use this type of solution. What is the clue that tells
you that the best solution is by wrapping one select statement inside of
another select statement.
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
(snip)
>Haven't tried that yet, but wouldn't the grouping be incorrect as you are
>looking at the value of the column instead of the derived value of that
>expression from the column?
Hi Tom,
Yes, you're right. I forgot that. GROUP BY all columns used in the
expresion makes for a valid query, but it'll only produce the same
results if no two sets of column values can ever result in the same
result of the expression.

>Not really sure why you can't use the assigned title of the column( x as
>column).
This has to do with the "official" way to process a SELECT. Official,
bacuase that's how ANSI says it should be done. In quotes, because all
major databases will choose other orders to optimize for speed, as long
as the results are the same as if the official order had been used.
Step 1: Process FROM clause (includes all JOIN clauses). Results in a
temporary table (stored internally) that holds all columns of all tables
used in the FROM clause, with all rows that satisfy the JOIN conditions.
If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
will hold the Carthesian product of the tables.
Step 2: Process WHERE clause. Check WHERE clause for each row in temp
table from step 1, and remove row if WHERE clause evaluates to FALSE or
UNKNOWN.
Step 3: Process GROUP BY clause. Using table from step 2, form groups of
rows that share the same value for all columns (or expressions) in the
GROUP BY clause.
Step 4: Process HAVING clause. Check HAVING clause for each *group of
rows* in the temp table after step 3, and remove *complete group* if
HAVING clause evaluates to FALSE or UNKNOWN.
Step 5: Process SELECT clause. Result of this step will be a table with
one column for each entry in the SELECT clause. If no GROUP BY is
present, than result set will have one row for each row left in the temp
table. If a GROUP BY is present, than result set will have one row for
each *group* of rows left in the temp table, and expression in the
SELECT list can't refer to columns/expressions not in the GROUP BY list,
unless enclosed in an aggregate function.
Since the SELECT is processed last, neither the result of the expression
nor the column alias given to it is available when the GROUP BY is
processed.

>That was also what Peter suggested, which worked in this example.
I don't see a message by Peter - had I known that you've already been
given this advise, I wouldn't have repeated it. Was Peter's reply in
this thread?

>I also looked at setting up views as we did before in my other problem, but
>it seemed like overkill in this problem. I am just trying figure out at
>what point I would use this type of solution. What is the clue that tells
>you that the best solution is by wrapping one select statement inside of
>another select statement.
In situations like this, I use a derived table if I have to repeat a
complicated expression. If I also might want to use the same expression
in other queries, I might go for a view. If it's this query only, I
prefer a derived table. If the expression is fairly simple, I just
repeat it.
Those are the rules of thumb. Readability and maintainability of code
are very important too, of course. And for the final decision, at least
when it's in code that needs to be fast, you'll have to test ... test
... test.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hc25m1tmpkh1tdrr17n3oi2fp0e0btpqb5@.
4ax.com...
> On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
> (snip)
> Hi Tom,
> Yes, you're right. I forgot that. GROUP BY all columns used in the
> expresion makes for a valid query, but it'll only produce the same
> results if no two sets of column values can ever result in the same
> result of the expression.
>
> This has to do with the "official" way to process a SELECT. Official,
> bacuase that's how ANSI says it should be done. In quotes, because all
> major databases will choose other orders to optimize for speed, as long
> as the results are the same as if the official order had been used.
> Step 1: Process FROM clause (includes all JOIN clauses). Results in a
> temporary table (stored internally) that holds all columns of all tables
> used in the FROM clause, with all rows that satisfy the JOIN conditions.
> If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
> will hold the Carthesian product of the tables.
> Step 2: Process WHERE clause. Check WHERE clause for each row in temp
> table from step 1, and remove row if WHERE clause evaluates to FALSE or
> UNKNOWN.
> Step 3: Process GROUP BY clause. Using table from step 2, form groups of
> rows that share the same value for all columns (or expressions) in the
> GROUP BY clause.
> Step 4: Process HAVING clause. Check HAVING clause for each *group of
> rows* in the temp table after step 3, and remove *complete group* if
> HAVING clause evaluates to FALSE or UNKNOWN.
> Step 5: Process SELECT clause. Result of this step will be a table with
> one column for each entry in the SELECT clause. If no GROUP BY is
> present, than result set will have one row for each row left in the temp
> table. If a GROUP BY is present, than result set will have one row for
> each *group* of rows left in the temp table, and expression in the
> SELECT list can't refer to columns/expressions not in the GROUP BY list,
> unless enclosed in an aggregate function.
> Since the SELECT is processed last, neither the result of the expression
> nor the column alias given to it is available when the GROUP BY is
> processed.
>
That makes sense.

>
I assume the derived table is the inner select?
> I don't see a message by Peter - had I known that you've already been
> given this advise, I wouldn't have repeated it. Was Peter's reply in
> this thread?
>
No, it was in the next one - which was a similar question. His result was:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
I have no problem seeing another similar answer as it helps to see what is
actually happening to see it from a couple of different angles, even if the
result is the same.

> In situations like this, I use a derived table if I have to repeat a
> complicated expression. If I also might want to use the same expression
> in other queries, I might go for a view. If it's this query only, I
> prefer a derived table. If the expression is fairly simple, I just
> repeat it.
Where it was difficult is trying to figure out that I need to create a
temporary table (derived - I assume) and then do a select on that.
Also, why do you need the "AS Der" (in your example)?
It's not used anywhere. I know that Peter did the same thing with his (AS
a)
Thanks,
Tom
> Those are the rules of thumb. Readability and maintainability of code
> are very important too, of course. And for the final decision, at least
> when it's in code that needs to be fast, you'll have to test ... test
> ... test.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 31 Oct 2005 16:23:35 -0800, tshad wrote:
(snip)
>I assume the derived table is the inner select?
Yes.

>No, it was in the next one - which was a similar question. His result was
:
>
(snip query)
That query doesn't look too efficient. I know you didn't ask about this
one, but is there any reason why you can't rewrite it as:
SELECT ProductName,
SUM(PostingsLeft) AS Balance,
SUM(CASE WHEN Age > 0 AND Age <= 30
THEN PostingsLeft ELSE 0 END), 0) AS [30],
SUM(CASE WHEN Age > 30 AND Age <= 60
THEN PostingsLeft ELSE 0 END), 0) AS [60],
SUM(CASE WHEN Age > 60 AND Age <= 90
THEN PostingsLeft ELSE 0 END), 0) AS [90]
FROM (SELECT ProductName,
DATEDIFF(day, CURRENT_TIMESTAMP, DateExpires) AS Age
FROM PurchasedProducts) AS a
GROUP BY ProductName
(snip)
>Also, why do you need the "AS Der" (in your example)?
>It's not used anywhere. I know that Peter did the same thing with his (AS
>a)
The syntax requires it. Each column used in a query must be addressable
by tablename-or-alias + columname. Though it is permitted to leave out
the tablename (or alias) in the actual references, it must still be
known to the database engine.
Since a derived table has no own table name, it can only be referenced
through an alias. That's why the syntax REQUIRES you to use an alias
after each derived table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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 Week

Hi,
I have a matrix with month and week as the column grouping. Somehow
the week gets displayed twice when half of the week falls at the end
of the month and the other half in the beginning of the following
month. How do i set it so that the week only displays once?
TIA.
JMset the hide duplicates property on the cell property
mike
"Jordan" wrote:
> Hi,
> I have a matrix with month and week as the column grouping. Somehow
> the week gets displayed twice when half of the week falls at the end
> of the month and the other half in the beginning of the following
> month. How do i set it so that the week only displays once?
> TIA.
> JM
>|||Hi Mike,
I've tried that but the it only hides the header. In the end i still
have 2 columns displayed but one without the week number.
Thanks,
JM
"mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> set the hide duplicates property on the cell property
> mike
> "Jordan" wrote:
> > Hi,
> >
> > I have a matrix with month and week as the column grouping. Somehow
> > the week gets displayed twice when half of the week falls at the end
> > of the month and the other half in the beginning of the following
> > month. How do i set it so that the week only displays once?
> >
> > TIA.
> >
> > JM
> >|||Hi,
Here's my situation. After configuring the report to group by month
and by week, here's the output.
| January | Febuary
---
| WK 1 | WK 2 | WK 3 | WK 4 | WK 4 | WK 5 |
As illustrated here, there's a repeat of WK 4 because part of week 4
continues in febuary. Is there any way i can rectify this and only
display WK 4 under january or whichever month that has the most days
of that particular week?
Thanks.
Regards,
JM
jordanm@.37.com (Jordan) wrote in message news:<9ae5dece.0411181827.18b1289c@.posting.google.com>...
> Hi Mike,
> I've tried that but the it only hides the header. In the end i still
> have 2 columns displayed but one without the week number.
> Thanks,
> JM
>
> "mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> > set the hide duplicates property on the cell property
> >
> > mike
> >
> > "Jordan" wrote:
> >
> > > Hi,
> > >
> > > I have a matrix with month and week as the column grouping. Somehow
> > > the week gets displayed twice when half of the week falls at the end
> > > of the month and the other half in the beginning of the following
> > > month. How do i set it so that the week only displays once?
> > >
> > > TIA.
> > >
> > > JM
> > >|||The answer to issue is posted here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=9d321a2a-cc24-4999-be05-1bca5950dee1&mid=9d321a2a-cc24-4999-be05-1bca5950dee1
hth, cheers,
Cos
"Jordan" wrote:
> Hi,
> Here's my situation. After configuring the report to group by month
> and by week, here's the output.
> | January | Febuary
> ---
> | WK 1 | WK 2 | WK 3 | WK 4 | WK 4 | WK 5 |
> As illustrated here, there's a repeat of WK 4 because part of week 4
> continues in febuary. Is there any way i can rectify this and only
> display WK 4 under january or whichever month that has the most days
> of that particular week?
> Thanks.
> Regards,
> JM
> jordanm@.37.com (Jordan) wrote in message news:<9ae5dece.0411181827.18b1289c@.posting.google.com>...
> > Hi Mike,
> >
> > I've tried that but the it only hides the header. In the end i still
> > have 2 columns displayed but one without the week number.
> >
> > Thanks,
> > JM
> >
> >
> > "mike" <mike@.discussions.microsoft.com> wrote in message news:<1D104872-83DE-4D65-AD38-33A3EF0B8511@.microsoft.com>...
> > > set the hide duplicates property on the cell property
> > >
> > > mike
> > >
> > > "Jordan" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a matrix with month and week as the column grouping. Somehow
> > > > the week gets displayed twice when half of the week falls at the end
> > > > of the month and the other half in the beginning of the following
> > > > month. How do i set it so that the week only displays once?
> > > >
> > > > TIA.
> > > >
> > > > JM
> > > >
>