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

Group no. of records by text in a text/varchar field

Create table Test
(Text1 varchar(500))
insert Test values('I love SQL')
insert Test values('SQL rocks')
insert Test values('SQL rocks in 2005')
insert Test values('MS rocks too')
insert Test values('MS is short for microsoft')
So i want to run a query where I would like to group by some key text words
..
So i want to get a count of entries in the table that has words 'SQL' and
'MS' in it
Output should be
KeyWord Count
MS 2
SQL 3
What is the query ? I would eventually add more keywords to the query..
Thanksyou'd want to unpack your input string into a table then it's just a matter
of finding the occurrences.
e.g.
declare @.s varchar(100)
set @.s='MS,SQL'
declare @.padded varchar(8000);set @.padded=','+@.s+','
select s,count(*)
from (select
substring(@.padded,digit+1,charindex(',',
@.padded,digit+1)-digit-1)
from racdigits
where digit <= len(@.padded)-1
and substring(@.padded,digit,1)= ',') derived(s)
join Test on Test.Text1 like '%'+derived.s+'%'
group by s
racdigits is just an auxilary table with value from 1-8000 (i.e. select top
8000 digit=identity(int,1,1) into racdigits from sysobjects,syscolumns)
-oj
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5HDQH2jGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text
> words ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks
>
>|||Where do you want to show data?
If you use front end application, split data there
Madhivanan
Hassan wrote:
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text word
s
> ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks|||On Tue, 13 Jun 2006 20:22:47 -0700, Hassan wrote:

>Create table Test
>(Text1 varchar(500))
>insert Test values('I love SQL')
>insert Test values('SQL rocks')
>insert Test values('SQL rocks in 2005')
>insert Test values('MS rocks too')
>insert Test values('MS is short for microsoft')
>So i want to run a query where I would like to group by some key text words
>..
>So i want to get a count of entries in the table that has words 'SQL' and
>'MS' in it
>Output should be
>KeyWord Count
>MS 2
>SQL 3
>What is the query ? I would eventually add more keywords to the query..
Hi Hassan,
Store the keywords in a seperate table, then use a query such as this:
SELECT k.Keyword, COUNT(t.Text1)
FROM Keywords AS k
LEFT JOIN Test AS t
ON t.Text1 LIKE '%' + k.Keyword + '%'
GROUP BY k.Keyword
Hugo Kornelis, SQL Server MVP

Group by Time interval.

I have a table(work_order) with time as varchar(5).

The values in table looks like this

work_order_id rtim

1 08:15
2 08:45
3 10:13
4 14:56

and so on...

I want to count how many work orders for every half an hour.

The result should look like this

Hours Count
8 10
8:30 15
9 34
9:30 03

and so on...

really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.

Any Help is Appreciated.

Thankyou.
Jaidev ParuchuriBetter to store your times as a DATETIME column:

CREATE TABLE Work_Order (work_order_id INTEGER PRIMARY KEY, rtim DATETIME
NOT NULL)

INSERT INTO Work_Order VALUES (1, '2003-11-11T08:15:00')
INSERT INTO Work_Order VALUES (2, '2003-11-11T08:45:00')
INSERT INTO Work_Order VALUES (3, '2003-11-11T10:13:00')
INSERT INTO Work_Order VALUES (4, '2003-11-11T14:56:00')

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',rtim)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

If you have to keep the Rtim column as CHAR:

SELECT mi,
COUNT(*)
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',
CONVERT(DATETIME,rtim,108)
)/30.0)*30
,'20000101'),108)
FROM Work_Order) AS W(mi)
GROUP BY mi

--
David Portas
----
Please reply only to the newsgroup
--|||"Jaidev Paruchuri" <jaidev@.criticalresourcetech.com> wrote in message
news:f885ab3.0311110746.7084cc95@.posting.google.co m...
> I have a table(work_order) with time as varchar(5).
> The values in table looks like this
> work_order_id rtim
> 1 08:15
> 2 08:45
> 3 10:13
> 4 14:56
> and so on...
> I want to count how many work orders for every half an hour.
> The result should look like this
> Hours Count
> 8 10
> 8:30 15
> 9 34
> 9:30 03
> and so on...
> really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.
> Any Help is Appreciated.
> Thankyou.
> Jaidev Paruchuri

CREATE TABLE Work_Orders
(
work_order_id INT NOT NULL PRIMARY KEY,
rtim CHAR(5) NOT NULL
)

SELECT Hrs.h + Sep.s + Mins.begin_min AS start_time,
COUNT(rtim) AS order_count
FROM (SELECT '00' AS h UNION ALL SELECT '01' AS h UNION ALL
SELECT '02' AS h UNION ALL SELECT '03' AS h UNION ALL
SELECT '04' AS h UNION ALL SELECT '05' AS h UNION ALL
SELECT '06' AS h UNION ALL SELECT '07' AS h UNION ALL
SELECT '08' AS h UNION ALL SELECT '09' AS h UNION ALL
SELECT '10' AS h UNION ALL SELECT '11' AS h UNION ALL
SELECT '12' AS h UNION ALL SELECT '13' AS h UNION ALL
SELECT '14' AS h UNION ALL SELECT '15' AS h UNION ALL
SELECT '16' AS h UNION ALL SELECT '17' AS h UNION ALL
SELECT '18' AS h UNION ALL SELECT '19' AS h UNION ALL
SELECT '20' AS h UNION ALL SELECT '21' AS h UNION ALL
SELECT '22' AS h UNION ALL SELECT '23' AS h) AS Hrs
CROSS JOIN
(SELECT ':' AS s) AS Sep
CROSS JOIN
(SELECT '00' AS begin_min, '29' AS end_min
UNION ALL
SELECT '30' AS begin_min, '59' AS end_min) AS Mins
LEFT OUTER JOIN
Work_Orders AS WO
ON rtim BETWEEN Hrs.h + Sep.s + Mins.begin_min AND
Hrs.h + Sep.s + Mins.end_min
GROUP BY Hrs.h + Sep.s + Mins.begin_min

Regards,
jag|||John

This query is beyond Excellence !!

This is exactly what i need.

Thank you for valuable your time!

regards,
--Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
David,

Your Query worked fine.
I didnt look at it earlier.

Thankyou very much .
Jaidev Paruchuri

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Group by query returning too many rows

Can anyone help ?
Why is this query:

select bp, sum(msg) as 'msg'
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

returning 666 rows, while this query

select bp
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

is only returning 20.
The "correct" result is 20 rows, one for each bp.
The fisrt query returns alot of duplicate bp.

By the way: What is faster: Converting the sqldate field to a varchar and comparing with another varchar, or converting the varchar to a date and then comparing it to the sqldate field ?do you want the SUM of all the msg attributes or the number of msg (messages?) for each bp? try using COUNT(*) in place of the SUM(msg).

In your case you are probably doing table scans due to the convertion of the date attribute to a varchar. SQL Server can efficiently convert and test a (var)char variable to a date attribute + you can take advantage of indexes.|||Sorry, I guess I should have made things clearer.
What I want is the sum of all the numbers stored in the msg column, i.e. the number of msgs for each bp. So the result set should have one row for each bp. This works fine without the sum(msg) part, and the result looks something like this:

HENNES
HENTEXTRA
KANAL5

But when I add the sum(msg) to get the number of messages pr. bp then the result looks like this:

Wow. Something strange just happened. When I ran the query to produce the results I added "order by bp" at the end, and then there was suddenly just one row for each 20 in total. Without it the resultset returns 666 rows.

Is the group by clause dependent upon the order one retrieves the rows ?|||No. The order by is used to sort the result set and does not affect the group by.

I set up a simple test...

Code:
----------------------------
create table #tmp(f1 varchar(10),f2 int)
insert into #tmp values('A',2)
insert into #tmp values('A',4)
insert into #tmp values('C',3)
insert into #tmp values('C',1)
insert into #tmp values('C',1)
insert into #tmp values('B',2)
insert into #tmp values('B',3)
insert into #tmp values('B',4)

select f1,sum(f2) as 'Sum'
from #tmp
group by f1
order by f1
----------------------------

is this anything close to what you are working on?|||That is pretty much what I am working on, except that my view has alot more columns. At the moment I am really only interested in getting one row for each bp, with one sum of messages for each.
My query does produce the desired results, as long as I have the "order by bp" clause at the end.
So my problem is really solved, but I don't really understand why though. If you want to find out why, and need any more information from me just let me know.
The view I am querying is based on two other views, but I can't see that making much of a difference.

This is the result I was looking for, and I get with the order bp:

davinci 1333
E-CLIPS 1864
HENNES 1397
KANAL5 6470
MRJET 6
PASSAGEN 70
SIMONTV 12
SPORTAL 828
STARLIFE 1004
TISCALI 2484
YAHOO 3
...
...
20 rows in total

This is some of what I get without the order bp:
SPORTAL 8
davinci 11
E-CLIPS 11
davinci 1
E-CLIPS 1
davinci 7
E-CLIPS 7
davinci 9
E-CLIPS 9
davinci 2
E-CLIPS 2
davinci 8
...
...
...
666 rows in total

Group by on a concatenate field

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by ?

How can I group by the field that I selected as CODE?

you have to repeat the expression:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

|||Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

But... why do you add an empty string in between? And do you mean LIKE 'F%', rather than LIKE 'F' ?

Don't underestimate the power of table expressions. The optimiser knows what you mean (so there's very little difference to performance), and you can easily make something far more readable that way. You could also have used a CTE, like this:

with c as (select CONVERT(VarChar(10),Service_Prefix)+ '' +

CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount,

Service_Prefix from

dbo.Enterprise_Credits_Import_90_days)
select top 100 code, date_issued, sum(amount)

from c
where code <> '0'

and Service_Prefix like 'F'

group by code, date_issued

Rob|||

Rob Farley wrote:

Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

SQL Server does not allow grouping by column alias names - does it? Other RDB engines allow this, and even ordinal grouping (group by 1,2, ...) notations but I thought SQL Server required you to repeat the expressions.

|||But this isn't actually grouping by a column alias. It's grouping by a field in a table expression. It would be no different if you created a view which had those fields in it - then you wouldn't see a problem using the view's fields to group by...

By wrapping the fields up in a table expression, you can easily circumnavigate the restrictions on SQL Server to repeat those expressions.

Rob|||right - I should have looked more closely at your original query - I didn't see the inline view before...|||:) So has it helped?|||

Im going to test it today. Ill let you know if I get it to work.

Thanks for alll the help !

|||

I tried the first statement and was able to make it work. Thanks for the info.

The second query gave me this error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.

Also due to the fact that the database has around 6 million records the group by's seem to make the query take a while to run. Is there any way to speed up the query?

Thanks again for your help

|||The second one requires SQL2005 - you'll get an error in SQL2000.

Could you get away with grouping by service_prefix and service_code separately, and then only concatenating them in the final select? That way, you could put a useful index on those two fields (plus date_issued, and amount), and it should run much more nicely. A covering index which includes all the fields you're interested in will mean that it doesn't even need to look at the table, because all the info will exist in the index.

Like this:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code <> ' 0' and Service_Prefix like 'F%'
group by service_code, service_prefix, date_issued

And you have an index on service_code, service_prefix, date_issued, amount

By changing the where clause to filter on the service_code rather than the concatenated field, that will help performance too, because it can then use the index more effectively. If you need to cater for where the 0 can be either in the prefix or in the code, you could always do an extra check... but try to avoid grouping or filtering on calculated fields, which makes it harder for the system to use indexes.

Rob|||

I think I see what you are saying here. The group by on both non concatenated fields should produce the same results as one group by by using the indexes more effectively. Ill try this and let you know. Prob be tommorow. Although in order to do a group by I thought you had to have the the group by fields in the select statement?

Thanks for your help.

|||You don't need to have the group by fields in the select statement, you just can't use fields that aren't either aggregated or one of the grouped fields.

But you can certainly select a concatenation of two of the grouped fields - definitely no problem there.

Rob|||

I would be careful about grouping on such a value as this:

Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

Instead of doing all of this conversion stuff in the bowels of the query do it either:

1. In the user interface and write the query as:

Select TOP 100 Service_Prefix, Service_Code, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

2. If you cannot use the UI, then do the conversion in an aggregate. There will be little performance hit because there will only be a single row in every case:

Select TOP 100 Max(CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code)) as CODE,
Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

ANY code appearing in an expression in a where or group by clause (and having, join-on criteria, etc) can cause performance issues that cannot be solved with indexes, like the criteria:

CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0'

Will not perform well because you don't have an index on this expression, and the optimizer would have to figure it out over and over instead of a simple probe into an index.

|||Yes... this is the point I was trying to make.

One thing to do it as an intellectual exercise about what you can do with T-SQL, but as soon as you're talking about performance, then you need to consider the fact that you really don't want to use the results of functions for filters/groups/sorts.

Rob|||Not disagreeing with you...Just adding my 2 cents worth to clarify/back you up :)

GROUP BY column-name problem (need expression)

Please advise on how to get the GROUP BY coded in an acceptable way:

DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)

SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO= 250
SELECT @.HI= 333

SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages= SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount= SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateYou don't need the non-column expressions in the GROUP BY list. Column
aliases aren't permitted either. Try:

...
GROUP BY statecd, countycd, zip, Z.cityname, Z.statecode, Z.countyname

--
David Portas
SQL Server MVP
--|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.

group by and sum

Hi,
I have a table like this;
WAREHOUSE_STOCKS
--
WAREHOUSE_NAME (varchar)
ITEM_NAME (varchar)
LOT_NAME (varchar)
QUANTITY (int)
and these are records,
| WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
Wr-1, It-1, Lt-1, 10
Wr-1, It-1, Lt-2, 20
Wr-1, It-2, Lt-1, 10
Wr-2, It-1, Lt-1, 5
Wr-2, It-1, Lt-2, 20
Wr-2, It-1, Lt-3, 10
I want to write SQL and show sum(quantity) "group by" warehouse and item,
like this;
| WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
Wr-1, It-1, 30
Wr-1, It-2, 10
Wr-2, It-1, 35
How can I do?
thanksSeems to be a simple GROUP BY with aggregate function:
SELECT warehouse_name, item_name, SUM(quantity) AS quantity
FROM warehouse_stock
GROUP BY warehouse_stock, item_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||Try,
select WAREHOUSE_NAME, ITEM_NAME, sum(QUANTITY) as sum_QUANTITY
from dbo.WAREHOUSE_STOCKS
group by WAREHOUSE_NAME, ITEM_NAME
See "select statement" in BOL.
AMB
"Yunus Efe" wrote:

> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>|||select WAREHOUSE_NAME , ITEM_NAME, sum(QUANTITY )
from WAREHOUSE_STOCKS
group by WAREHOUSE_NAME , ITEM_NAME
"Yunus Efe" <Yunus Efe@.discussions.microsoft.com> wrote in message
news:5D27FE26-FACE-476C-B639-B170A894383E@.microsoft.com...
> Hi,
> I have a table like this;
> WAREHOUSE_STOCKS
> --
> WAREHOUSE_NAME (varchar)
> ITEM_NAME (varchar)
> LOT_NAME (varchar)
> QUANTITY (int)
> and these are records,
> | WAREHOUSE_NAME | ITEM_NAME | LOT_NAME | QUANTITY |
> Wr-1, It-1, Lt-1, 10
> Wr-1, It-1, Lt-2, 20
> Wr-1, It-2, Lt-1, 10
> Wr-2, It-1, Lt-1, 5
> Wr-2, It-1, Lt-2, 20
> Wr-2, It-1, Lt-3, 10
> I want to write SQL and show sum(quantity) "group by" warehouse and item,
> like this;
> | WAREHOUSE_NAME | ITEM_NAME | QUANTITY |
> Wr-1, It-1, 30
> Wr-1, It-2, 10
> Wr-2, It-1, 35
> How can I do?
> thanks
>

GROUP BY and populating temp table, ideas?

Hello All,
I have the following table, and want to create a report as seen below.
CREATE TABLE [dbo].[Sales] (
[ACTIVITY_ID] [varchar] (16) NOT NULL ,
[CREATED_BY] [varchar] (10) NULL,
[YEAR] [varchar] (9) NULL ,
[PERIOD] [varchar] (2) NULL ,
[WEEK] [char] (1) NULL ,
[AMOUNT] [varchar] (3) NULL
) ON [PRIMARY]
GO
I need to see count(*) of each rep for each w and current preriod.
Something like this:
REP_NAME W1 W2 W3 W4 W5 Period(Month)
======== ===== ===== ===== ===== ===== =============
DAVID 5 10 5 20
WILLIAM 2 8 5 15
JANE 10 2 10 22
Do I need to run seperate group by's for each w and populate a temp table
?
Or there can be a simpler way to do that?
Thanks,
Ada
--
SQL Server DBAFirst of all, pivoting data for presentation purposes does not belong on the
data layer.
But if you really, really, really need to do it in T-SQL read this:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML

GROUP BY and ORDER BY

hi Guys!
I am having a table
customer {
Name varchar(255),
step int,
details varchar(255)
}
I am trying to get the list of customer name with GROUP BY, and order
it using ORDER BY
SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
any suggestions?Hi
You'll have to include a step column within a SELECT stratement (at least)
and then ORDER BY this column
<sharma.vasudev@.gmail.com> wrote in message
news:1133327048.081452.157790@.z14g2000cwz.googlegroups.com...
> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||Hi Vasudev,
There is a problem with Table design, looking at the query you are executing
.
Try the following query. Please note that I am using Step in select list
(for same error u got).
SELECT Distinct Name,Step FROM customer ORDER BY step
Vishal Khajuria
9886170165
IBM Bangalore
"sharma.vasudev@.gmail.com" wrote:

> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||ok, i did that with following query
SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?|||hi Vishal,
i am really new with SQL stuff, kinda newbie, I am not sure what you
are pin-pointing to when you said 'There is a problem with Table
design' i would really appreciate if you could explain me a bit?
/dev|||To make things easy for everyone, can you just post some sample rows and
expected result?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"hack_tick" <sharma.vasudev@.gmail.com> wrote in message
news:1133336153.369184.296940@.o13g2000cwo.googlegroups.com...
> ok, i did that with following query
> SELECT Name, step from customer group by Name order by step
> it came out with following error,
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'customer.step is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> so i also tried GROUP BY
> SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
> but as expected it returned the result which was of no use to me :(
> any suggestions?
>|||Hi Vasudev,
The point to be noticed here is that the table does not conform to rules of
normalization. You should have one more table for Name of customers.
As per your question you were trying to get list of cusotmer names by using
Group By clause --
I did not see any reason why do you need group by clause.
What I could guess is that you want distinct names of customer, that is why
I suggested query with distinct and not group by. But here also you need to
keep step in Select clause.
Please let me know if you have any more questions.
--
Vishal Khajuria
9886170165
IBM Bangalore
"hack_tick" wrote:

> hi Vishal,
> i am really new with SQL stuff, kinda newbie, I am not sure what you
> are pin-pointing to when you said 'There is a problem with Table
> design' i would really appreciate if you could explain me a bit?
> /dev
>|||hi Vishal
The Table used was just for explanation, I am having a much bigger
table with 100's of columns and many complex relationship, also the
format of table is fixed and not suppose to change :(
I shall post a complete test case with insert in a while :)|||hi guys! maybe you all can have a look at the following query!
I need to have the name of the customer displayed, but they have to be
order by column 'step'
create table customer (
name varchar(255),
step int,
details varchar(255)
)
insert into customer values('cust-1', 1, 'details-1')
insert into customer values('cust-2', 2, 'details-2')
insert into customer values('cust-2', 3, 'details-3')
insert into customer values('cust-2', 4, 'details-4')
insert into customer values('cust-3', 5, 'details-5')
insert into customer values('cust-3', 6, 'details-6')
insert into customer values('cust-3', 3, 'details-7')
insert into customer values('cust-1', 4, 'details-8')
insert into customer values('cust-4', 5, 'details-9')
insert into customer values('cust-4', 1, 'details-10')
insert into customer values('cust-5', 1, 'details-11')
insert into customer values('cust-6', 3, 'details-11')
select name from customer group by name
i have tried following queries with following error
1) SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
2) SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?
PS: Sorry for any redundant data from my earlier post, just wanted to
have a common place for your all to look at :)|||what I want is to have the List of UNIQUE Customer name, but they have
to be sorted using field 'step'

group by and min()

I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
int, others varchar. Assume the following data.
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
I need to group by c3 and return the c2 that corresponds with the min()
of c1. I know I can write a simple function like below but would like
to know if there's a better way... ie no function.
select min(c1), dbo.getr2(c1, c3), c3
from table
group by c3
results
1 d22 d111
3 d33 d333
Thanks in advance.
jghwhat about select c3, min(c1) from table group by c3
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<justingharvey@.yahoo.com> wrote in message
news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
>I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I need the c2 that corresponds with the min(c1).
tks
Hilary Cotter wrote:[vbcol=seagreen]
> what about select c3, min(c1) from table group by c3
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <justingharvey@.yahoo.com> wrote in message
> news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...|||Hi,
Is what you what the C2 corresponding to the minimum value of C1 for each
distinct value of C3?
If so the following may be along the right lines:-
DROP TABLE Test
CREATE TABLE Test
(
C1 int,
C2 varchar(5),
C3 varchar(5)
)
INSERT INTO Test
(C1, C2, C3)
VALUES
(2, 'd11', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(1, 'd22', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(3, 'd33', 'd333')
SELECT C2
FROM TEST INNER JOIN
( SELECT MIN(C1) AS C1, C3
FROM TEST
GROUP BY C3
) AS MinC1ForEachC3
ON Test.C1 = MinC1ForEachC3.C1
AND Test.C3 = MinC1ForEachC3.C3
It might be what you need ... if not let us know
Craig
"justingharvey@.yahoo.com" wrote:

> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I'd do it like this:
select c1, c2, c3 from table
inner join
(
select min(c1) as Minc1, c3
from table
group by c3
) a
on table.c1 = a.Minc1
and table.c3 = a.c3
The function solution seems incorrect (or perhaps I didn't follow what your
function does), as the derivation needs to be applied after the grouping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||How about ...
select c2 from Table1 where C1 IN
(
select min(c1) from Table1 group by c3
)
"justingharvey@.yahoo.com" wrote:

> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||It wouldn't work. If we add another row to the table (at the bottom):
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
2 d44 d444
The subquery returns 1,2,3 so the outer query now returns d11,d22,d33,d44,
but d11 shouldn't be returned.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

group by and min()

I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
int, others varchar. Assume the following data.
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
I need to group by c3 and return the c2 that corresponds with the min()
of c1. I know I can write a simple function like below but would like
to know if there's a better way... ie no function.
select min(c1), dbo.getr2(c1, c3), c3
from table
group by c3
results
1 d22 d111
3 d33 d333
Thanks in advance.
jghwhat about select c3, min(c1) from table group by c3
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<justingharvey@.yahoo.com> wrote in message
news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
>I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I need the c2 that corresponds with the min(c1).
tks
Hilary Cotter wrote:
> what about select c3, min(c1) from table group by c3
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <justingharvey@.yahoo.com> wrote in message
> news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
> >I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> > int, others varchar. Assume the following data.
> >
> > c1 c2 c3
> > --
> > 2 d11 d111
> > 1 d22 d111
> > 3 d33 d333
> >
> > I need to group by c3 and return the c2 that corresponds with the min()
> > of c1. I know I can write a simple function like below but would like
> > to know if there's a better way... ie no function.
> >
> > select min(c1), dbo.getr2(c1, c3), c3
> > from table
> > group by c3
> >
> > results
> > 1 d22 d111
> > 3 d33 d333
> >
> >
> >
> > Thanks in advance.
> >
> > jgh
> >|||Hi,
Is what you what the C2 corresponding to the minimum value of C1 for each
distinct value of C3?
If so the following may be along the right lines:-
DROP TABLE Test
CREATE TABLE Test
(
C1 int,
C2 varchar(5),
C3 varchar(5)
)
INSERT INTO Test
(C1, C2, C3)
VALUES
(2, 'd11', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(1, 'd22', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(3, 'd33', 'd333')
SELECT C2
FROM TEST INNER JOIN
( SELECT MIN(C1) AS C1, C3
FROM TEST
GROUP BY C3
) AS MinC1ForEachC3
ON Test.C1 = MinC1ForEachC3.C1
AND Test.C3 = MinC1ForEachC3.C3
It might be what you need ... if not let us know :)
Craig
"justingharvey@.yahoo.com" wrote:
> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I'd do it like this:
select c1, c2, c3 from table
inner join
(
select min(c1) as Minc1, c3
from table
group by c3
) a
on table.c1 = a.Minc1
and table.c3 = a.c3
The function solution seems incorrect (or perhaps I didn't follow what your
function does), as the derivation needs to be applied after the grouping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||How about ...
select c2 from Table1 where C1 IN
(
select min(c1) from Table1 group by c3
)
"justingharvey@.yahoo.com" wrote:
> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||It wouldn't work. If we add another row to the table (at the bottom):
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
2 d44 d444
The subquery returns 1,2,3 so the outer query now returns d11,d22,d33,d44,
but d11 shouldn't be returned.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Group by

I have a table defined with the following field names.
TableID Varchar(20)
CrcNbr Int
RegionName Varchar(25)
Sample Data:
--
pr_mstr,11500,Test
pr_mstr,11500,Trng
prd_det,12000,Test
prd_det,12005,Trng
prd_det,12005,Prod
I want to produce a report from this data that gives me this info. Saying
In what Regions does the table exist and are there a difference in the
CrcNbr's.
Table-Name Test Trng Prod Test/Trng Crc
Trng/Prod Crc
pr_mstr Y Y N Match
If 'N' under Prod tab leave this blank
prd_det Y Y Y Test Doesn't match Trng
MatchHi
It is usually better to do this one the client, but if not you can self join
the table and use case to determine the Ys or Ns such as:
SELECT T.Name,
CASE WHEN D1.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Test,
CASE WHEN D2.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Trng,
CASE WHEN D3.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Prod,
CASE WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr <>
D2.CrcNbr THEN 'No Matches'
WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr =
D2.CrcNbr THEN 'Trng Matches Prod'
...
END AS [Crc Checks]
FROM MyTables T
LEFT JOIN MyData d1 on T.TableId = D1.TableId AND D1.RegionName = 'Test'
LEFT JOIN MyData d2 on T.TableId = D2.TableId AND D2.RegionName = 'Trng'
LEFT JOIN MyData d3 on T.TableId = D2.TableId AND D3.RegionName = 'Prod'
John
"Hoosbruin" wrote:

> I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc
> Trng/Prod Crc
> pr_mstr Y Y N Match
> If 'N' under Prod tab leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||hi,
Select TableName,
Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
Case When Sum (Case When RegionName = 'Test' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Test' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Test/Trng',
Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Prod' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Prod/Trng'
From <YourTable>
Group by TableName
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc Trng/Prod Crc
> pr_mstr Y Y N Match If 'N' under Prod tab
> leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||Thanks...
worked GREAT !!!!!!
"arik" <arikf@.top4.com> wrote in message
news:OmQytdcjFHA.3580@.TK2MSFTNGP09.phx.gbl...
> hi,
> Select TableName,
> Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
> Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
> Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
> Case When Sum (Case When RegionName = 'Test' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Test' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Test/Trng',
> Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Prod' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Prod/Trng'
> From <YourTable>
> Group by TableName
>
> "Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
> news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>