Grouping Select Statements with where clause

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.

campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>No you don't. You just think you do. What you really want to do is create a CROSSTAB query. Look it up in Books Online.sql

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

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping in columns rather than rows using table control?

Is there a way to transform the table object to display group data in columns
instead of rows? Here is my example:
Report services table can do this when grouping on YEAR
[1 GROUP Header (YEAR)
[Header
[ BODY Parameter1 Parameter 2 Parameter 3
[FOOTER
[1 GROUP Footer (SUM)
Example
Year 2000
Mike John Mary
5 1 4
5 2 2
SUM 10 3 6
Year 2001
Mike John Mary
1 6 5
2 2 2
SUM 3 8 7
What I want is this:
[ Group Header ] [Table Header] [DATA] [Table Footer] [Group
Footer]
YEAR Parameter 1
SUM
Parameter 2
Parameter 3
2000 SUM 2001 SUM
Mike 5 5 10 1 2 3
John 1 2 3 6 2 8
Mary 4 2 6 5 2 7
So the idea is to group by Year but display the SUMs in a column not in a
row. I just can't figure out how to use the Matrix control, I want to use the
table control functionality but with column output.
Thanksyou can use a matrix to do just that
"Ramez" wrote:
> Is there a way to transform the table object to display group data in columns
> instead of rows? Here is my example:
> Report services table can do this when grouping on YEAR
> [1 GROUP Header (YEAR)
> [Header
> [ BODY Parameter1 Parameter 2 Parameter 3
> [FOOTER
> [1 GROUP Footer (SUM)
> Example
> Year 2000
> Mike John Mary
> 5 1 4
> 5 2 2
> SUM 10 3 6
> Year 2001
> Mike John Mary
> 1 6 5
> 2 2 2
> SUM 3 8 7
> What I want is this:
> [ Group Header ] [Table Header] [DATA] [Table Footer] [Group
> Footer]
> YEAR Parameter 1
> SUM
> Parameter 2
> Parameter 3
> 2000 SUM 2001 SUM
> Mike 5 5 10 1 2 3
> John 1 2 3 6 2 8
> Mary 4 2 6 5 2 7
> So the idea is to group by Year but display the SUMs in a column not in a
> row. I just can't figure out how to use the Matrix control, I want to use the
> table control functionality but with column output.
> Thanks

grouping data

some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table

ex:-
Id Name Value
-------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3

I need it in this way

x y z
----
a1 b1 c1
a2 b2 c2
a3 b3 c3


(no of columns in the new table can't be pre determined)

and which one would be better option to do this
in VB.Net code or in a Storedprocedure?You can do this in a stored procedure using CASE statements. You can find a fine explanation of the method by searching for "Cross-Tab Reports" in Books online.|||Normally I'd recommend that you do this on the client, but it can (usually) be done on the server. Just to prove that, I wrote a snippet of code that seems to work, although it doesn't deal well with ill-behaved data. FWIW, my code is:CREATE TABLE tPivot (
id INT NOT NULL
, name VARCHAR(20) NOT NULL
, value VARCHAR(20) NOT NULL
)

INSERT tPivot (id, name, value) VALUES (1, 'x', 'A1')
INSERT tPivot (id, name, value) VALUES (2, 'x', 'A2')
INSERT tPivot (id, name, value) VALUES (3, 'x', 'A3')

INSERT tPivot (id, name, value) VALUES (1, 'y', 'B1')
INSERT tPivot (id, name, value) VALUES (2, 'y', 'B2')
INSERT tPivot (id, name, value) VALUES (3, 'y', 'B3')

INSERT tPivot (id, name, value) VALUES (1, 'z', 'C1')
INSERT tPivot (id, name, value) VALUES (2, 'z', 'C2')
INSERT tPivot (id, name, value) VALUES (3, 'z', 'C3')

DECLARE @.cmd NVARCHAR(4000)
DECLARE @.cName SYSNAME

SELECT @.cmd = 'SELECT DISTINCT id'

DECLARE zName CURSOR FOR SELECT DISTINCT
name
FROM tPivot

OPEN zName
FETCH zName INTO @.cName

WHILE 0 = @.@.fetch_status
BEGIN
SELECT @.cmd = @.cmd +
', (SELECT Min(value) FROM tPivot AS b'
+ ' WHERE b.id = a.id'
+ ' AND b.name = ''' + @.cName
+ ''') AS [' + @.cName + ']'

FETCH zName INTO @.cName
END

CLOSE zName
DEALLOCATE zName

SELECT @.cmd = @.cmd + ' FROM tPivot AS a'

SELECT @.cmd
EXECUTE (@.cmd)Note that even though this CAN be done on the server, it would be better handled on the client side in most cases.

-PatP|||Hey Pat, you just love cursors, don't you?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select [x], [y], [z] from (
select distinct ID from @.tbl) t1
left outer join (
select ID, [x] = Value from @.tbl where Name = 'x') t2
on t1.ID = t2.ID
left outer join (
select ID, [y] = Value from @.tbl where Name = 'y') t3
on t1.ID = t3.ID
left outer join (
select ID, [z] = Value from @.tbl where Name = 'z') t4
on t1.ID = t4.ID|||Originally posted by rdjabarov
Hey Pat, you just love cursors, don't you? Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

-PatP|||Who loves left-joins and subqueries?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Originally posted by theguru
(no of columns in the new table can't be pre determined)
Was this part of the spec optional ?

-PatP|||Originally posted by Pat Phelan
Not hardly, hate 'em with a passion. Unfortunately when I read the specs, I couldn't think of another way to deal with unknown column names.

-PatP

I saw the spec and stayed away...

Blenderized data anyone?

Salt or No Salt?|||Originally posted by Brett Kaiser
Salt or No Salt? Stayed away ? Don't you mean you're wasting away... oops, wait a sec, you're headed there anyway!

-PatP|||Not until 5:00 Pat...

Hey, look at that...it's 5:00!

See ya....|||"(no of columns in the new table can't be pre determined)"

Crap. Always read the fine print...

theguru, I've seen posts for fully dynamic SQL code that will do on-demand cross tabs, though I haven't tested them. rdjabarov, didn't you have one? It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released.|||Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||Originally posted by blindman
It is definitely advanced SQL programming, so if one of these other gentlemen cannot refer you to some prewritten code, I suggest you try to accomplish this in VB.Net, or wait for SQL Server Yukon to be released. At least I think that is what my code sample does. That is exactly why I had to resort to a cursor to build dynamic code, even though both of those go against my better judgement!

-PatP|||Whoa! Please go get your cig!

People ask this question because
A) They are trying to format the data for reporting
and
2) They were weaned on MS Access and its wonderfully convenient and fully dynamic cross-tab functionality.

Problem is, theguru, that when you don't know the number of columns or the names of the columns, most reporting applications (such as Crystal or even MS Access' reports) will choke on the output.

Perhaps your best bet would be to load the data into a pivot table in flat-file format, and then slice-and-dice however you want. How pretty does the output need to be?

Pat Phelan, I like your idea, though I haven't tried it out. I'd call it semi-dynamic, since you are working with a defined table format. A fully-dynamic method applicable to any dataset is the Holy Grail of cross-tab reporting.|||Originally posted by rdjabarov
Isn't it indicative of both poor app and poor database design? I'm not prepared to argue that point. We'd both be "preaching to the choir" on this one!

Originally posted by rdjabarov
But I'd just stress the first one, - who in their sober mind would design an application that would produce such output? What makes you think that the designer was in their sober mind ? ;)

-PatP|||Hey, at least my problems can be answered with a cig! You guys are sitting on a poor design and kicking this horse with "non-determined" number of legs wondering if it's gonna ever run again (tsk, it's been dead for a while...)|||See what happens when we both start typing really fast?|||Originally posted by rdjabarov
See what happens when we both start typing really fast? Yeah, but it's fun to watch!

-PatP|||Thank you all for u r replies.
Well "the number of columns cannot be pre..." doesn't mean that no of columns may exceed a 2 digit number atmost 20 columns.
ok I will try this out in stored procedure with my actual data.

thanks once again..keep sending u r suggestions...

Originally posted by rdjabarov

Whenever I see something like "the number of columns cannot be pre..." I just can't believe there is a developer that can actually buy it! You mean to say, that the number of columns can be 432347656? Or even more realistic, like 2972? Isn't it indicative of both poor app and poor database design? But I'd just stress the first one, - who in their sober mind would design an application that would produce such output?|||sorry, I think I might be missing some thing here.
This will work when I am sure of occurance x,y,z names exactly in the table
but it is not case here x,y,z may be reffered with some other names like A,B,C or O,P,Q which can't be presumed.

Originally posted by blindman
Who loves left-joins and subqueries?

declare @.tbl table (
ID int not null,
Name char(1) not null,
Value char(2) not null)
insert @.tbl values(1, 'x', 'a1')
insert @.tbl values(2, 'x', 'a2')
insert @.tbl values(3, 'x', 'a3')
insert @.tbl values(1, 'y', 'b1')
insert @.tbl values(2, 'y', 'b2')
insert @.tbl values(3, 'y', 'b3')
insert @.tbl values(1, 'z', 'c1')
insert @.tbl values(2, 'z', 'c2')
insert @.tbl values(3, 'z', 'c3')

select max(case when Name = 'x' then Value end) as x,
max(case when Name = 'y' then Value end) as y,
max(case when Name = 'z' then Value end) as z
from @.tbl
group by ID|||Great...I go out and slam some 'ritas...get called back in...yeah there;s a concept..to fix a prod problem and everyone is going nuts...

The point is mute...

It's still bender data...

No?|||Just curious at this point, but have you tried my code with your data?

-PatP|||yup,
thank u.but I am waiting for some more options.
any way I will use this one for the time being.
thank u.


Originally posted by Pat Phelan
Just curious at this point, but have you tried my code with your data?

-PatP|||Just a note...

1. I'm exhausted...

2. What's the point of your result set? It makes no sense.

Is this homework?|||This is what i need to show to my client.
I can't change the DB design at this point.I have to do this at any cost, performance is an exception for this.

Originally posted by Brett Kaiser
Just a note...

1. I'm exhausted...

2. What's the point of your result set? It makes no sense.

Is this homework?

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 Distinct

Hi,
I have 2 columns of data, one has an Agent code and the other has
information about the Agent. There are duplicates of the Agent code in the
1st column, but different info in the second. For example:
Col 1 Col 2
Agent 1 Data 1
Agent 1 Data 2
Agent 1 Data 3
Agent 2 Data 4
Agent 2 Data 5
Is there a way to only show the Agent once without duplicating it? I dont
want to sum or count anything, I just want to show the data like this:
Agent 1 Data 1
Data 2
Data 3
Agent 2 Data 4
Data 5
Does anyone know if this grouping is possible?
Thanks,
What you describe is more like a report than a query
result, but you can produce reports in SQL. One way
to do it is like this:
select
Col1, Col2
from (
select
Col1 as hidden1, Col1,
min(Col2) as hidden2, min(Col2) as Col2
from T
group by Col1
union all
select
Col1, '', Col2, space(2) + Col2
from T
where Col2 <> (
select min(Col2) from T as Tm
where Tm.Col1 = T.Col1
)
) T
order by hidden1, hidden2
-- Steve Kass
-- Drew University
PML wrote:

>Hi,
>I have 2 columns of data, one has an Agent code and the other has
>information about the Agent. There are duplicates of the Agent code in the
>1st column, but different info in the second. For example:
>Col 1 Col 2
>Agent 1 Data 1
>Agent 1 Data 2
>Agent 1 Data 3
>Agent 2 Data 4
>Agent 2 Data 5
>Is there a way to only show the Agent once without duplicating it? I dont
>want to sum or count anything, I just want to show the data like this:
>Agent 1 Data 1
> Data 2
> Data 3
>Agent 2 Data 4
> Data 5
>Does anyone know if this grouping is possible?
>Thanks,
>
>

Grouping and TOP 10

I have a table (medical data relating to the number of patients admitted into a ward with different illnesses) which has 3 columns I am interested in.

Column 1 is Ward,
Column 2 is the diagnosis,
Column 3 is the nuber of patients admitted into the ward with the diagnosis.

Firstly, i know this data is not relational (it is a warehouse and we only have flat files) which i think might be what is causing my problems.

What i want to do is write a query that will give me the top 10 for each ward based on the number of patient admitted

eg.

Ward Diagnosis Number of Patients
######################################
1 Broken Leg 107
1 Broken Hip 98
1 Broken Nose 56
...
2 Lung Cancer 105
2 Liver Cancer 65
...
etc

Does anybody know how to do this as i keep going round in circles and i can't work out how to do it.

Thanks in advance,

Emma.Which DBMS? Some (e.g. Oracle) have "analytic functions" that make this sort of query easy. Without analytics, you could do something like:

select ward, diagnosis, num_patients
from table t1
where 10 > (select count(*) from table t2 where t1.ward = t2.ward and t2.num_patients > t1.num_patients);|||SQL Server back-end but i am running the query in an access front end (so either would be fine as i can put in in the front of back)|||and i worked it out from a post on the SQL Server board:

SELECT *
FROM tab AS a
WHERE ((a.num_patients) In (select top 3 num_patients from tab b where a.ward = b.ward))
ORDER BY a.Ward, a.num_patients DESC;

Thanks for the help.sql

grouping and sorting in matrix control

I have a dataset returned from sql server that can be represented for the
purpose of this discussion with 2 columns. From the server all the data is
sorted first by column 1 and then by column 2 so that the resultset looks
like the following:
column1, column2, column3
a, 1/1/2007, 10
a, 2/1/2007, 30
a, 3/1/2007, 15
b, 10/1/2006, 5
b, 11/1/2006, 1
b, 12/1/2006, 100
b, 1/1/2007, 10
b, 2/1/2007, 9
c, 11/1/2006, 22
c, 12/1/2006, 33
c, 1/1/2007, 44
When I put this data into a matrix with the dates making the columns and
column1 values for each row I get the following
1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
a 10 30 15
b 10 9 5 1
100
c 44 22
33
what I want is the following:
10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
a 10
30 15
b 5 1 100 10 9
c 22 33 44
With the dates sorted. I know I can do it by changing the stored proc but
that opens up all sorts of issues with other things. Is there any way to get
the data looking like I want using reporting services and not modifying the
stored proc?
thanksOn Feb 28, 2:11 pm, Brian <B...@.discussions.microsoft.com> wrote:
> I have a dataset returned from sql server that can be represented for the
> purpose of this discussion with 2 columns. From the server all the data is
> sorted first by column 1 and then by column 2 so that the resultset looks
> like the following:
> column1, column2, column3
> a, 1/1/2007, 10
> a, 2/1/2007, 30
> a, 3/1/2007, 15
> b, 10/1/2006, 5
> b, 11/1/2006, 1
> b, 12/1/2006, 100
> b, 1/1/2007, 10
> b, 2/1/2007, 9
> c, 11/1/2006, 22
> c, 12/1/2006, 33
> c, 1/1/2007, 44
> When I put this data into a matrix with the dates making the columns and
> column1 values for each row I get the following
> 1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
> a 10 30 15
> b 10 9 5 1
> 100
> c 44 22
> 33
> what I want is the following:
> 10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
> a 10
> 30 15
> b 5 1 100 10 9
> c 22 33 44
> With the dates sorted. I know I can do it by changing the stored proc but
> that opens up all sorts of issues with other things. Is there any way to get
> the data looking like I want using reporting services and not modifying the
> stored proc?
> thanks
>From your results, it looks like column2 is sorting aphabetically (I'm
assuming that column2 is not defined as a datetime field in the report
or dataset). You should be able to use the conversion function CDate()
in your sort expression. Something like this should work: CDate(Fields!
column2.Value) and the direction should be ascending. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

grouping a few columns

/*
Hi
I need to query this table to get results where ids are found with every
searchNum, i.e. the results of this would be:
id
1
2
because both id 1 and 2 are found with searchNum 1,2,3. The table could be
any size with any variation of ids and searchNum so I need some sort of
general grouping query. Hope this makes sence. I've been bashing my head
against the wall all day.
thanks Andrew
*/
declare @.table table (searchNum int, word varchar(50), id int)
insert into @.table values (1, 'cambridge', 1)
insert into @.table values (1, 'northampton', 2)
insert into @.table values (1, 'hull', 4)
insert into @.table values (2, 'laboratory', 1)
insert into @.table values (2, 'chemistry', 2)
insert into @.table values (2, 'chemistry', 5)
insert into @.table values (2, 'laboratory', 2)
insert into @.table values (2, 'laboratory', 4)
insert into @.table values (3, 'scientist', 1)
insert into @.table values (3, 'scientist', 2)
select * from @.tableJ055 wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
>
Thanks for posting the DDL and sample data. Please do also include keys
and constraints with your DDL. It can make a big difference to the
solution. Here's one suggestion:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(DISTINCT searchnum)
FROM @.table);
If searchnum is a foreign key you could also reference the other table:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(*)
FROM search);
David Portas
SQL Server MVP
--|||Try this:
SELECT [id] FROM
(
SELECT id, COUNT(*) AS NofRecs
FROM (SELECT DISTINCT searchNum, [id] FROM @.table) AS inn
GROUP BY [ID]
HAVING COUNT(*) IN
(
SELECT COUNT( DISTINCT searchNum ) FROM @.table
)
) AS cnt
"J055" wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
> */
> declare @.table table (searchNum int, word varchar(50), id int)
> insert into @.table values (1, 'cambridge', 1)
> insert into @.table values (1, 'northampton', 2)
> insert into @.table values (1, 'hull', 4)
> insert into @.table values (2, 'laboratory', 1)
> insert into @.table values (2, 'chemistry', 2)
> insert into @.table values (2, 'chemistry', 5)
> insert into @.table values (2, 'laboratory', 2)
> insert into @.table values (2, 'laboratory', 4)
> insert into @.table values (3, 'scientist', 1)
> insert into @.table values (3, 'scientist', 2)
> select * from @.table
>
>
>|||This is division, the usual approach is:
SELECT id
FROM ( SELECT id, COUNT( DISTINCT searchnum)
FROM tbl
GROUP BY id ) D ( id, num )
WHERE ( SELECT COUNT(DISTINCT searchnum)
FROM tbl ) = num ;
Anith

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

Grouping

I'm trying to write a report and need to group columns that are alike.
For example.

DeptID JobTitle

40344 Sales Clerk
1st Assistant
Store Manager

40666 Sales Clerk
2nd Assistant
Store Manager

Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.Why? This is just a display issue. Can't you handle it in the frontend?|||Not if its just a query. I was thinking about doing the whole cursor deal and looping through that to give me my desired results but i thought it would be an easier way.|||You could do it as two selects. Select into a table variable with an identity column. Then select from the table variable and show blank if it's not the max(identity) for the given DeptID. Make sense?|||Yes, thank you.|||What about appending a carriage return to the last field: + char(13)?|||What about appending a carriage return to the last field: + char(13)?If you pursue that route, you need to "crelf it"... Instead of just a carriage return, you need a carriage return followed by a line feed which is: + Char(13) + Char(10)

-PatP

GROUPBY ??

I have a table with two columns item_code and Alternate_reference. During a
select I would like to concatenate Alternate_reference if there are more tha
n
one for an Item_code. Is this possible and if so how do you accomplish this.
Here is and sample of the data
Item Code Alternate_reference
123456 85694
123456 86623
123456 25364
The resulting value
Item Code Alternate_reference
123456 85694, 86623, 25364See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Sherry" wrote:

> I have a table with two columns item_code and Alternate_reference. During
a
> select I would like to concatenate Alternate_reference if there are more t
han
> one for an Item_code. Is this possible and if so how do you accomplish thi
s.
> Here is and sample of the data
>
> Item Code Alternate_reference
> 123456 85694
> 123456 86623
> 123456 25364
>
> The resulting value
> Item Code Alternate_reference
> 123456 85694, 86623, 25364|||Sherry, I've needed to do something like this for user display purposes
and I've done it in application/script code. Very simple loop and
concat operation. Probably not as simple in SQL, so if you can do it
client-side you'll be better off.|||Thanks Alehandro...I'll look it over.
"Alejandro Mesa" wrote:
> See if this helps:
> http://groups-beta.google.com/group...d85bf366dd9e73e
>
> AMB
> "Sherry" wrote:
>|||Alejandro and Sherry have giving you the correct answer but let me repeat
it.
Do this client side.
Reasons:
Databases are not built to do this efficiently (it's not their job).
It's simple to do this client side.
Your application will possible (probably) be faster if you do this client
side.
I tested the last point out myself and the speed-gain was remarkable.
YMMV
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:65EF5552-90E1-4122-990D-B27E4BD8225D@.microsoft.com...
>I have a table with two columns item_code and Alternate_reference. During
>a
> select I would like to concatenate Alternate_reference if there are more
> than
> one for an Item_code. Is this possible and if so how do you accomplish
> this.
> Here is and sample of the data
>
> Item Code Alternate_reference
> 123456 85694
> 123456 86623
> 123456 25364
>
> The resulting value
> Item Code Alternate_reference
> 123456 85694, 86623, 25364

Group/Details Formatting

Hello,

I am developing a report where i have one group e.g. Dealer Name and then details of that dealer.

Dealer details can have around 10 columns and where dealer name is of around 80 chars length. I need to use 10.7in*7.1in page size.

So how i can manage the formatting for dealer group, dealer details in one page.

Thanks.

Hi Amit,

It sounds like you think you can't put the "long" group name on top of the table. Is that right?

If so, try this:

set up your group on the dealer name, as you normally would, in a table now you have your column headers for each of the detail columns, as you usually would|||

Thanks!!....I added one table row and merge the cell. In Detail section for identation i added " "/Space(3)...

group into 1 row

I have a query that I need to group the results for a particular client into
1 row. The problem is that I'm creating some columns on the fly using Case
statements, and the columns contain a Y or N as their data. Because of
this, I can't get the results in 1 row. I want to know if there is a way to
do this.
I've come up with a sample query that uses the Northwind db that will show
you what I'm trying to do. I'd like the results of the query below in 1
row. I know it doesn't make sense if you look at the result for the query
below, but it's just a representative example for what I'm doing.
select CategoryName,
Drink1 = Case ProductName when 'Chai' then 'Y' end,
Drink2 = Case ProductName when 'Chang' then 'Y' end
from [Products by Category]
where ProductName in ('chai','chang')
Thanks, Andresomething like this?
select CategoryName,
Drink1 = max(Case ProductName when 'Chai' then 'Y' end),
Drink2 = max(Case ProductName when 'Chang' then 'Y' end)
from [Products by Category]
where ProductName in ('chai','chang')
group by categoryName
I used max because the other value is only null. Use suitably for your issue
.
Hope this helps.|||Yes, exactly like that! Thanks.

GROUP BY/select list error

I am trying to get a rollup report with several summaries included, but I
keep getting errors that complain about columns not in the GROUP by included
in the select list , even though are not. They are used in a subquery and a
join however. If I use the integer IDs the query works fine, but I need to
order by the names rather than IDs so I get an alphabetical report.
Given the snippet that follws can anyone tell me what might be causing the
issue and how I can correct it?
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision
WITH ROLLUP
Results in the errors:
Msg 8120, Level 16, State 1, Line 1
Column 'P.LotPropertyID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'S.LotSubdivisionID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause."Byron" <Byron@.discussions.microsoft.com> wrote in message
news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
>I am trying to get a rollup report with several summaries included, but I
> keep getting errors that complain about columns not in the GROUP by
> included
> in the select list , even though are not. They are used in a subquery and
> a
> join however. If I use the integer IDs the query works fine, but I need
> to
> order by the names rather than IDs so I get an alphabetical report.
> Given the snippet that follws can anyone tell me what might be causing the
> issue and how I can correct it?
>
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> JOIN dbo.tbl_LotSubdivisions S on S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision
> WITH ROLLUP
>
> Results in the errors:
> Msg 8120, Level 16, State 1, Line 1
> Column 'P.LotPropertyID' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Msg 8120, Level 16, State 1, Line 1
> Column 'S.LotSubdivisionID' is invalid in the select list because it is
> not
> contained in either an aggregate function or the GROUP BY clause.
You are trying to referencing unaggregated columns in the subquery:
...
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
That won't work. I can only guess what you intended by this query. Try the
following but if that's not it please post DDL, sample data and show your
required end result.
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(*) AS lots
FROM dbo.tbl_Lots L (NOLOCK)
JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
S.LotSubdivision
WITH ROLLUP ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Below are the tables involved, less many of the uninvolved columns.
Properties have Lots and Subdivisions and Lots are assigned to both a
Property and one of its Subdivisions. The general idea is to get the number
of Lots in each Subdivision subtotalled by Property, followed by a grand
total. The problem is that I have to group by integer IDs, but want the
results displayed by Property name, further broken out by Subdivision name.
For example:
Property 1 Subdivision 1 23
Property 1 Subdivision 2 15
Property 1 38
Property 2 Subdivision 1 10
Property 2 Subdivision 2 10
Property 2 20
All Properties 58
CREATE TABLE [dbo].[tbl_LotProperties](
[LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyName] [nvarchar](128))
CREATE TABLE [dbo].[tbl_LotSubdivisions](
[LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivision] [nvarchar](100))
CREATE TABLE [dbo].[tbl_Lots](
[LotID] [int] IDENTITY(1,1) NOT NULL,
[LotPropertyID] [int] NOT NULL,
[LotSubdivisionID] [int] NOT NULL)
"David Portas" wrote:

> "Byron" <Byron@.discussions.microsoft.com> wrote in message
> news:029AD83E-DE44-4B6F-B689-89345A12FF72@.microsoft.com...
> You are trying to referencing unaggregated columns in the subquery:
> ...
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> That won't work. I can only guess what you intended by this query. Try the
> following but if that's not it please post DDL, sample data and show your
> required end result.
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(*) AS lots
> FROM dbo.tbl_Lots L (NOLOCK)
> JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, P.LotPropertyName,
> S.LotSubdivision
> WITH ROLLUP ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||Just add the integer IDs to the select list (generally). It's a major
problem that you don't have any PRIMARY KEYs set on your tables. I
assume that the PKs are the "ID"s, though. Just make sure that you
define your data that way. So, you could do:
SELECT
P.LotPropertyName,
S.LotSubdivision,
-- LotCount
'Lots' =
( SELECT Count(*)
FROM dbo.tbl_Lots L (NOLOCK)
WHERE L.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1) -- Lot
FROM dbo.tbl_LotProperties P (NOLOCK)
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
GROUP BY
P.LotPropertyID,
P.LotPropertyName,
S.LotSubDivisionID,
S.LotSubdivision
WITH ROLLUP
but like Dave Portas alluded to, here's a better way (btw, I am "reading
in" to your requirements a bit here):
SELECT
P.LotPropertyName,
S.LotSubdivision,
COUNT(DISTINCT L.LotID) AS Lots
FROM dbo.tbl_Lots L (NOLOCK)
INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
ON L.LotPropertyID = P.LotPropertyID
INNER JOIN dbo.tbl_LotSubdivisions S
ON S.LotPropertyID = P.LotPropertyID
AND L.LotSubdivisionID = S.LotSubdivisionID
WHERE P.IsEnabled = 1
AND L.IsEnabled = 1
AND L.IsDeleted = 0
AND L.InventoryTypeID = 1
AND S.IsEnabled = 1
GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
WITH ROLLUP;
Byron wrote:
> Below are the tables involved, less many of the uninvolved columns.
> Properties have Lots and Subdivisions and Lots are assigned to both a
> Property and one of its Subdivisions. The general idea is to get the numb
er
> of Lots in each Subdivision subtotalled by Property, followed by a grand
> total. The problem is that I have to group by integer IDs, but want the
> results displayed by Property name, further broken out by Subdivision name
.
> For example:
> Property 1 Subdivision 1 23
> Property 1 Subdivision 2 15
> Property 1 38
> Property 2 Subdivision 1 10
> Property 2 Subdivision 2 10
> Property 2 20
> All Properties 58
> CREATE TABLE [dbo].[tbl_LotProperties](
> [LotPropertyID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyName] [nvarchar](128))
> CREATE TABLE [dbo].[tbl_LotSubdivisions](
> [LotSubdivisionID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivision] [nvarchar](100))
> CREATE TABLE [dbo].[tbl_Lots](
> [LotID] [int] IDENTITY(1,1) NOT NULL,
> [LotPropertyID] [int] NOT NULL,
> [LotSubdivisionID] [int] NOT NULL)
>
> "David Portas" wrote:
>|||I apparently erred by simplifying my example code to save space. There are
actually primary keys on the integer IDENTITY columns, and there are many
other columns I eliminated from the CREATE code, leavong only the keys and
names. There are also about a dozen other subqueries in addition to the one
that counts the lots, though all of them use data in the Lots table and need
to be grouped by Property and Subdivision. All of them work fine as long as
I group by ID, but I need to return the results to the user with the Propert
y
and Subdivision names sorted by name rather than ID. Since I ran into so
much trouble using GROUP BY I started down the path of using temporary table
s
and a cursor to iterate through an intermediate result set adding the
subtotals and grand total, but I realize there must be a better way to do it
.
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> Just add the integer IDs to the select list (generally). It's a major
> problem that you don't have any PRIMARY KEYs set on your tables. I
> assume that the PKs are the "ID"s, though. Just make sure that you
> define your data that way. So, you could do:
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> -- LotCount
> 'Lots' =
> ( SELECT Count(*)
> FROM dbo.tbl_Lots L (NOLOCK)
> WHERE L.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1) -- Lot
> FROM dbo.tbl_LotProperties P (NOLOCK)
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> WHERE P.IsEnabled = 1 AND S.IsEnabled = 1
> GROUP BY
> P.LotPropertyID,
> P.LotPropertyName,
> S.LotSubDivisionID,
> S.LotSubdivision
> WITH ROLLUP
> but like Dave Portas alluded to, here's a better way (btw, I am "reading
> in" to your requirements a bit here):
> SELECT
> P.LotPropertyName,
> S.LotSubdivision,
> COUNT(DISTINCT L.LotID) AS Lots
> FROM dbo.tbl_Lots L (NOLOCK)
> INNER JOIN dbo.tbl_LotProperties P (NOLOCK)
> ON L.LotPropertyID = P.LotPropertyID
> INNER JOIN dbo.tbl_LotSubdivisions S
> ON S.LotPropertyID = P.LotPropertyID
> AND L.LotSubdivisionID = S.LotSubdivisionID
> WHERE P.IsEnabled = 1
> AND L.IsEnabled = 1
> AND L.IsDeleted = 0
> AND L.InventoryTypeID = 1
> AND S.IsEnabled = 1
> GROUP BY P.LotPropertyName, S.LotSubdivision, L.LotID
> WITH ROLLUP;
>
> Byron wrote:
>|||Post your real DDL and query. You don't want to be using a cursor for
this...
Byron wrote:
> I apparently erred by simplifying my example code to save space. There ar
e
> actually primary keys on the integer IDENTITY columns, and there are many
> other columns I eliminated from the CREATE code, leavong only the keys and
> names. There are also about a dozen other subqueries in addition to the o
ne
> that counts the lots, though all of them use data in the Lots table and ne
ed
> to be grouped by Property and Subdivision. All of them work fine as long
as
> I group by ID, but I need to return the results to the user with the Prope
rty
> and Subdivision names sorted by name rather than ID. Since I ran into so
> much trouble using GROUP BY I started down the path of using temporary tab
les
> and a cursor to iterate through an intermediate result set adding the
> subtotals and grand total, but I realize there must be a better way to do
it.
>
>
> "Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
>

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 Restrictions?

Hi, Just a little doubt...
Is any difference or restriction there between using in the group by clause the columns as they are in the select list?

For example when I use an UPPER, CONVERT, etc.

Thanks : )

use pubs

--Case 1
select upper(title), type
from titles
group by upper(title), type

--Case 2
select upper(title), type
from titles
group by (title), type

Actually you don't have a GROUP BY query because a GROUP BY without an aggregate function is just a standard DISTINCT but SQL Server 2005 will not allow you to compile the code and ask you to add the aggregate function. Hope this helps.|||

In your case both the quires will be identical. (Upper & Lower)

When you use convert it may produce a different result..BUT YOU WONT GET ANY ERROR.

See the below sample,

Code Snippet

Create table #Test(

Date datetime,

StkQty int);

Insert into #Test Values('2007-01-01 10:00', 10)

Insert into #Test Values('2007-01-01 11:00', 100)

Insert into #Test Values('2007-01-02 12:00', 17)

Insert into #Test Values('2007-01-02 13:00', 13)

Select

Convert(varchar(10),Date,103)

,Sum(StkQty) as [Sum]

From

#test

Group By

Convert(varchar(10),Date,103)

/*

DateSum

01/01/2007 110

02/01/2007 30

*/

Select

Convert(varchar(10),Date,103)

,Sum(StkQty) as [Sum]

From

#test

Group By

Date

/*

DateSum

01/01/2007 10

01/01/2007 100

02/01/2007 17

02/01/2007 13

*/

|||

Yes I am sorry, it was a bad example. Suppose that I have made a select over some duplicated rows.

I need to make a kind of Catalog and want only distinct values, so as a way to do it I use the Group by Clause. Some times I use to apply "convert char", "case", "upper" over columns. I have seen some cases where Group By clause is showed exactly like the select list but just don't know the meaning.