How do I get a count of each set of results?

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.

i.e.

Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2

Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...

Is there straightforward way of doing this in SQL?

ThanksIn SQL Server 2005:

select
[Type],
count() over (partition by [Type]) as [Count]
from T

In SQL Server 2000:

select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T

(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)

Steve Kass
Drew University

kasterborus@.yahoo.com wrote:

Quote:

Originally Posted by

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
>
i.e.
>
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
>
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
>
Is there straightforward way of doing this in SQL?
>
Thanks
>

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP

How do I do this in SS2000?

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea

declare @.s varchar(8000)
select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.

How do I do an UPDATE in this complex query?

Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmdtn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only. (acmdtn_RECID is the primary key)
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to run reports on the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get all of the the latest "id" records as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.

I would suggest changing this stored procedure to a function or perhaps making a version of this that is a function. Hang on and I'll try to show you. Maybe something like:

create function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

|||

I mocked this up with this table and data:

create table dbo.acmdtn
( id integer,
efctv_from_dt datetime,
efctv_to_dt datetime,
modify_ts datetime,
extractno integer,
Active integer,
acmdtn_RECID integer
)
go

insert into acmdtn
select 1, '1/1/7', '2/1/7', '1/1/7', 1, 0, 1 union all
select 1, '2/1/7', '4/1/7', '1/15/7', 2, 0, 1 union all
select 1, '4/1/7', '1/1/8', getdate(), 3, 0, 1 union all
select 2, '1/1/7', '3/1/7', '1/1/7', 1, 0, 2 union all
select 2, '3/1/7', '7/1/7', '2/15/7', 2, 0, 2
select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 0 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 0 2
*/

I tested the UPDATE like this:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

go

update selectCurrent_Acmdtn (1)
set Active = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 2
*/

Something to consider is the use of:

SELECT dbo.acmdtn.*,

This is dangerous because it is not intuitively obvious whether or not this statement will return all columns of the dbo.acmdtn table. This is because the meaning of this select statement is determined at function compile time and NOT at function execution time. Therefore, I strongly suggest that you alter this statement to explicitly list all columns returned by the select statement.

This problem comes into play whenever the structure of the table is altered because at that time the columns returned by this select are no longer the same as the columns contained in the table.

In addition, if the point of this function is only to perform the update then it would be better to eliminate from the select statement any columns that do not contribute to the update.

|||

Thanks alot for the reply. I'll give that a try.

(I think I screwed up a little because I posted this question multiple times. I kept getting a message that "the administrator may have deleted your post" and I couldn't find it on a search initially, so I kept on posting!)

The only thing that I didn't mention is that I have acmdtn_RECID as the primary key (It's an identy field that gets assigned when I do the import from the raw data, because the original raw data didn't have any primary keys).

Would that change your soloution any?

Thanks again.

|||

No, it will not really change this solution; however, you are correct in identifying that my test data would not be valid. It would be good to have an index based on extractno because this is what is used here for filtering the data.

Again, it would be good to eliminate the SELECT * syntax to pare down some of the data.

|||

Create a stored procedure to apply the UPDATE.

create PROCEDURE [dbo].[update_Current_acmdtn]

@.extractNum char(10)

AS

SET NOCOUNT ON;

with cte

as

(

SELECT

dbo.acmdtn.*,

row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM

dbo.acmdtn

WHERE

extractno > @.extractNum

)

update cte

set Active = 1

where rn = 1

return @.@.error

go

AMB

|||

Hunchback presents a good way of performing the update. I realized that I need to factor out a piece from my query so I amended my function similar to what Hunchback did. Also, I eliminated the SELECT * syntax:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( select id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum

)

go

update selectCurrent_Acmdtn (1)
set Active = 1
where rn = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 2
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 11:10:56.793 3 0 3
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 4
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 5
*/

Thanks, Hunchback. :-)

|||

Thanks alot guys,

I got it to work.

How do I do a like '%<string>%' equivalent in FTS

How do the following equivalent query in FTS.

select brandName from Brand b where b.brandName like '%lf%'

returns "Alfa Brand"

select brandName from Brand b where CONTAINS (b.brandName, '"*lf*"')
returns Zero rows

pls help, I just can't FTS to return the row.

thx
jt

I can replicate this error. Your syntax is correct but it looks like the fulltext engine is ignoring the first wildcard (*)

Therefore your query turns into

select brandName from Brand b where CONTAINS (b.brandName, '"lf*"')

I'm not sure if this is standard behaviour across all installs of SQL. I'll see if i can find out.

|||I don't think FTS supports inter-word searching
|||There are Specific rules you will have to go with if you use FTS, depening on the wordbreaker you can use * at the beginning of a words. This depends on the wordbreaker which is used for the column / attribute.

e.g. if you search for *race in columns which contain TRACE and FASTRACE, you will find the second word as it was broken into FAST & RACE.

Jens K. Suessmeyer.

http://www.sqlserver2005.de