Create a string of records from a table in a stored procedure,

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401190748.491c6219@.posting.google.c om...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

Generally the best way to do this would be in a front end application, where
it's easier to handle string manipulation. But this thread may be useful if
you have no other choice than to do it in MSSQL:

http://tinyurl.com/bib2

Simon|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@.CustId as int) returns
varchar(100)
as
begin
declare @.ret_value varchar(100)
SET @.ret_value=''
Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
CustID=@.CustId
RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer|||Amit Gupta (amiiit@.hotmail.com) writes:
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end

Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Amit,
Thank you for your examples. I realize there is still a lot to learn
for with the SQL. I have never used "Create function" and don't know
where it goes. Sorry for the ignorance...
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||Specifically...
When I try to cretae a stored procedure containing this Create
Function, I get the error:
"You cannot chnage the object type in a script."
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||In the DAH! Department...
I realize I have to do the Create Function in Enterprise Manager. I've
been using Microsoft Access MSDE as the front end development tool...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947725BA51CYazorman@.127.0.0.1>...
> Amit Gupta (amiiit@.hotmail.com) writes:
> > /*Create a function to do the job*/
> > Create function dbo.fn_concatinate(@.CustId as int) returns
> > varchar(100)
> > as
> > begin
> > declare @.ret_value varchar(100)
> > SET @.ret_value=''
> > Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> > CustID=@.CustId
> > RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> > end
> Not that this function relies on undefined behaviour. It may return
> the expected result, or it may return something else. See
> http://support.microsoft.com/default.aspx?scid=287515.

Create a DTS package to retrieve records from db & schedule to send the email at

hi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.The thing that you are looking for can be done thru a "execute sql task" and using xp_sendmail. do you have any prior experience with DTS?
-rohit|||i got some prior experience in DTS but jus not sure how to do this in graphically . programming wise i know how to do it , i just want to get some knowledge how to do it in DTS|||Get in touch with http://www.sqldts.com website which has got plenty of DTS resources and code examples.

Also refer to books online for information about DTS as a first hand help.sql

Create a Condition for a DTS Task

I simply want an email to go out if any records in a table exist, else no email. I have other steps completed in this DTS job but this is the last step. Any ideas?
ddaveNever mind. I decided it be best to drop it into a stored procedure.

ddave

Crazy error when too many rows (records) are requested

Hi,

I am hoping someone here can help me out with this crazy error. I have an rdlc that works fine most of the time. When the user selects certain large date ranges and requests a huge amount of data we get this error:

HttpException (0x80072745): Unable to make the session state request to the session state server. Please ensure that the ASP.NET State
service is started and that the client and server ports are the same. If the server is on a remote machine, please ensure that it
accepts remote requests by checking the value of
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\aspnet_state\Parameters\AllowRemoteConnection. If the server is on the local
machine, and if the before mentioned registry value does not exist or is set to 0, then the state server connection string must use
either 'localhost' or '127.0.0.1' as the server name.]
System.Web.SessionState.OutOfProcSessionStateStore.MakeRequest(StateProtocolVerb verb, String id, StateProtocolExclusive
exclusiveAccess, Int32 extraFlags, Int32 timeout, Int32 lockCookie, Byte[] buf, Int32 cb, Int32 networkTimeout,
SessionNDMakeRequestResults& results) +1565
System.Web.SessionState.OutOfProcSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData
item, Object lockId, Boolean newItem) +192
System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +355
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Caveat; State Service is running.So, this solution is NOT germanehttp://forums.asp.net/t/1030680.aspx
Do to business requirements I cannot restrict the date ranges a user might enter

Any ideas, suggestions, work-arounds?? Thank you in advance!

I have finally figured out a "solution". Basically, I had to change how session was being managed. It was being manged by StateServer and I switched it to "InProc" and that seemed to do the trick.

Believe me I tried pretty muchEVERYTHING to get this to work and switching the session management was a last ditch, stab-in-the-dark try. And it worked. I think maybe there is some sort of bug with StateServer and rdlc files?

I was able to reproduce the error and the solution locally using a basic .aspx page with an rdlc control. I made a basic object for the data. Dumped a million rows onto the rdlc while running StateServer and got the error posted above. Then I just switched the session management to "InProc" and got the report to display the million rows. It took a bit of spinning but it eventually displayed. I hope this helps somebody out. Take care.

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.

CR with Left Joint and where condition

I have two tables like this

Table 1

tid
...

Table 2

tid
type
...

the relation is on the column tid of both the tables. We can have multiple records for a tid in Table2 or we may not have any record too.

I am able to get results from Table1 and Table2 by making left outer join in crystal reports (linking). But i have one problem.
I need to get only records in Table2 whose type (field) is "A". For this is achive I am going to select expert in Crystal reports and did type="A" but the problem is if Table2 is not having any records for a tid that tid record is not displaying. How to solve this issue.
I know if we do some thing like this type (+)="A" it will work but How can I do this in Crystal Reports.

Any help is apperciated.

Thanks
KalikiYou can use the add command property in the database expert and write manually your database query

select t.field1, r.field1, t.field2, r.field2
from tab1 t, tab2 r
where tab1.field3 = tab2.field3 (+)
and tab2.field4 (+) = 'A'

This works fine using CR version 10, if you are using an older version (8.5) you can type the 'and tab2.field4 (+) = 'A'' part of the condition to the SQL-query (DATABASE, EDIT SQL-query).

- Jukka|||Thanks for your response.
I am using 8.5 so i am editing the sql via (DATABASE, EDIT SQL-query).

CR prints no record although there is records in dbase

Dear all,

I found a problem and this makes me frustrated.
I'm using VB6 and CR 8, When I saved a transaction and print invoice, there is no data printed on CR (I'm sure the there is a record saved in database). The big matter is it's happened sometimes.
It runs ok for several transaction and happen again.
I'm really confused, I've tried to close all connection before printing invoice. But it still happened.
Please let me know what should I do.
Urgent..

Thank you.One thing that gave good result to me, was :
In VB6, before process the report:
Close the tables and database & execute DoEvents
ReOpen the database and tables (if needed) after preview or print the report

Counting Rows that refence another table

Hi,

I have two tables,

Table A(A_ID, Info)

Table B(B_ID, A_ID, Blah) where B.A_ID references A.A_ID

How can I detemine how many records in table B reference each unique A_ID in table A?

I've tried the following but it doesn't work:

Select A.A_ID, COUNT(B.A_ID) FROM A
JOIN B ON A.A_ID = B.A_IDI've figured out the counting using this

Select A.A_ID, COUNT(B.A_ID) FROM A
JOIN B ON A.A_ID = B.A_ID
GROUP BY A.A_ID

Just needed to add the 'group by'

Can anyone tell me how I can restrict this to only show cases where there are (for example) 3 or more records in table B that reference A_ID?|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTableA(A_Id int)
CREATE TABLE myTableB(B_Id int, A_Id int)
GO

INSERT INTO myTableA(A_Id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
INSERT INTO myTableB(B_Id, A_Id)
SELECT 1,1 UNION ALL SELECT 1,2 UNION ALL SELECT 1,3 UNION ALL
SELECT 2,1 UNION ALL SELECT 2,2 UNION ALL
SELECT 3,1
GO

SELECT a.A_Id, COUNT(*)
FROM myTableA a INNER JOIN myTableB b ON a.A_Id = b.B_Id
GROUP BY a.A_Id
GO

SET NOCOUNT OFF
DROP TABLE myTableA, myTableB
GO|||Having Count(*) > 2

counting records in all tables

Hello all,

I was wondering if there is anyway to find number of records in all tables instead of count one table at a time by select count(*) from table_name.

thanks

The system tables are good for this. They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

I use the following to get the row count, data bytes, and index bytes for all tables:

SELECT

sys.schemas.[name] AS [Schema],

sys.tables.name AS [Table],

COALESCE([Row Count].[Count], 0) AS [Rows],

COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

FROM sys.tables

INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

LEFT OUTER JOIN (

SELECT

object_id,

SUM(rows) AS [Count]

FROM sys.partitions

WHERE index_id < 2

GROUP BY object_id

) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

ORDER BY sys.tables.[name]

(The weird text editor on this site messed up the formatting of the code; I apologize)

-Ryan

|||

Hello Ryan and all,

I am using SQL Server 2000; therefore, I won't be able to see table as sys.schemas and sys.tables. Would you advise how to do this in SQL Server 2000 ?

thanks

|||You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

Just wondering: why are you interested in this information?

Thanks,|||

hello Marcel and all,

I know there will be no guarantee unless using select(*). However, estimate number would be fine. Because I am looking for large table to tune.

|||

Unfortunately, we're fully upgraded to SQL 2005 here (we were active in the beta and were ready to go when 2005 was released). So I can't accurately convert it back to 2000 code.

While SQL 2000 doesn't have a sys.tables, it does have a systables. It should be possible to get this working on the older version with minor adjustments.

-Ryan

|||This is a quick and dirty procedure that uses the results from

sp_spaceused to create a simple report. I've used it to weed out a few

space hogs on a 2000 server. Note that getting accurate row counts is

probably contingent upon having your usage statistics up to date (see

DBCC UPDATEUSAGE). Create the procedure in master, and run it from

within the database you want to profile, like other sp_ procedures.

Tweak it to suit your needs - I'm sure there's room for improvement.

CREATE PROCEDURE sp_tablesizes @.biggestfirst bit = 0
AS
-- sp_tablesizes
-- Dave Britten, 2007
-- Produces a list of all user and system tables in the current database,
-- optionally sorted by physical size. Also returns grand totals in a
-- second result set.

SET NOCOUNT ON
CREATE TABLE #tablelist (
Owner sysname,
Name sysname
)

CREATE TABLE #tabledata (
Name varchar(128),
Rows char(11),
reserved varchar(18),
Data varchar(18),
index_size varchar(18),
Unused varchar(18)
)

CREATE TABLE #sizedata (
rows int,
reserved int,
Data int,
index_size int,
Unused int
)

DECLARE @.dbname sysname
SET @.dbname = db_name()
DECLARE @.tablename sysname
DECLARE @.ownername sysname
DECLARE @.tablestring varchar(261)

INSERT INTO #tablelist
EXEC('SELECT sysusers.name, sysobjects.name FROM [' + @.dbname + ']..sysusers INNER JOIN [' + @.dbname + ']..sysobjects ON sysobjects.uid = sysusers.uid WHERE type IN (''U'', ''S'') AND sysobjects.name NOT LIKE ''tempdb..%''')

DECLARE tables CURSOR FAST_FORWARD FOR
SELECT Owner, Name FROM #tablelist
OPEN tables

FETCH NEXT FROM tables INTO @.ownername, @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tablestring = '[' + @.ownername + '].[' + @.tablename + ']'
INSERT INTO #tabledata
EXEC sp_spaceused @.tablestring
FETCH NEXT FROM tables INTO @.ownername, @.tablename
END

CLOSE tables
DEALLOCATE tables

INSERT INTO #sizedata (rows, reserved, data, index_size, unused)
SELECT
rows,
CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int),
CAST(LEFT(data, PATINDEX('% KB', data)) AS int),
CAST(LEFT(index_size, PATINDEX('% KB', index_size)) AS int),
CAST(LEFT(unused, PATINDEX('% KB', unused)) AS int)
FROM #tabledata

IF @.biggestfirst = 1
SELECT * FROM #tabledata ORDER BY CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int) DESC
ELSE
SELECT * FROM #tabledata ORDER BY name ASC

SELECT
SUM(rows) [Total Rows],
STR(SUM(reserved)) + ' KB' [Total reserved],
STR(SUM(data)) + ' KB' [Total Data],
STR(SUM(index_size)) + ' KB' [Total index_size],
STR(SUM(unused)) + ' KB' [Total Unused]
FROM #sizedata

DROP TABLE #tablelist
DROP TABLE #tabledata
DROP TABLE #sizedata

GO|||

Hello davidbrit2 and all,

Thank you davidbrit2 for the replied. When I complied the stored procedure, I got the problem at database name as a variable.

e.g: DECLARE @.dbName AS VARCHAR(30)

SELECT @.dbName = 'Another Database'

SELECT name FROM [' + @.dbName + ']..sysobjects WHERE type = 'U' ORDER BY name

Then I received error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '' + @.dbName + '..sysobjects'.

Please advise.

|||

For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

SELECT OBJECT_NAME(si.[id]), si.[rows]

FROM dbo.sysindexes si

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want to include the tables' owners then you could use this:

SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

FROM dbo.sysindexes si

INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]

INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want accurate row counts and can't be bothered creating and using a cursor then use this:

CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, TableRowCount

FROM #tmpOutput

ORDER BY 1

DROP TABLE #tmpOutput

Chris

|||

Here is the script that can give you the number of counts in each table -

Declare @.testSql varchar(300)
Create Table #TempTabCount(TABLE_QUALIFIER sysname NULL,
TABLE_OWNER sysname NULL,
TABLE_NAME sysname NULL,
NON_UNIQUE smallint NULL,
INDEX_QUALIFIER sysname NULL,
INDEX_NAME sysname NULL,
TYPE smallint NULL,
SEQ_IN_INDEX smallint NULL,
COLUMN_NAME sysname NULL,
COLLATION char(1) NULL,
CARDINALITY int NULL,
PAGES int NULL,
FILTER_CONDITION varchar(128) NULL)

Declare TAB_CURSOR CURSOR for
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES

Open TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @.testSql
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTabCount(TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION)
exec sp_statistics @.testSql
FETCH NEXT FROM TAB_CURSOR INTO @.testSql
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
Select TABLE_OWNER, TABLE_NAME, CARDINALITY from #TempTabCount

See if it solves your purpose. I tested it in SQL 2000 and working for me.

Enjoy!

Ash

|||Chris,

Ah-ha, it's sysindexes. I knew they had to be storing that information SOMEWHERE. ;)|||

Hi All,

Please find the query which will help to find the total number of records in all the tables in a SQL Server Database.

Select substring(obj.name, 1, 50) as Table_Name,
ind.rows as Number_of_Rows
from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id
where
obj.xtype = 'u'and ind.indid < 2
order by
obj.name

Regards
AKMEHTA

counting records in all tables

Hello all,

I was wondering if there is anyway to find number of records in all tables instead of count one table at a time by select count(*) from table_name.

thanks

The system tables are good for this. They're also faster than COUNT(*) for single tables, since SQL Server doesn't currently optimize that query.

I use the following to get the row count, data bytes, and index bytes for all tables:

SELECT

sys.schemas.[name] AS [Schema],

sys.tables.name AS [Table],

COALESCE([Row Count].[Count], 0) AS [Rows],

COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],

COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]

FROM sys.tables

INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id

LEFT OUTER JOIN (

SELECT

object_id,

SUM(rows) AS [Count]

FROM sys.partitions

WHERE index_id < 2

GROUP BY object_id

) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id

LEFT OUTER JOIN (

SELECT

sys.indexes.object_id,

SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [Count]

FROM sys.indexes

INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id

AND p.index_id = sys.indexes.index_id

INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY sys.indexes.object_id

) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id

ORDER BY sys.tables.[name]

(The weird text editor on this site messed up the formatting of the code; I apologize)

-Ryan

|||

Hello Ryan and all,

I am using SQL Server 2000; therefore, I won't be able to see table as sys.schemas and sys.tables. Would you advise how to do this in SQL Server 2000 ?

thanks

|||You are not guaranteed that the numbers you get back via this method will be correct. The best way to do it is to create a script that does a SELECT COUNT(*) FROM TABLE for all tables.

Just wondering: why are you interested in this information?

Thanks,|||

hello Marcel and all,

I know there will be no guarantee unless using select(*). However, estimate number would be fine. Because I am looking for large table to tune.

|||

Unfortunately, we're fully upgraded to SQL 2005 here (we were active in the beta and were ready to go when 2005 was released). So I can't accurately convert it back to 2000 code.

While SQL 2000 doesn't have a sys.tables, it does have a systables. It should be possible to get this working on the older version with minor adjustments.

-Ryan

|||This is a quick and dirty procedure that uses the results from

sp_spaceused to create a simple report. I've used it to weed out a few

space hogs on a 2000 server. Note that getting accurate row counts is

probably contingent upon having your usage statistics up to date (see

DBCC UPDATEUSAGE). Create the procedure in master, and run it from

within the database you want to profile, like other sp_ procedures.

Tweak it to suit your needs - I'm sure there's room for improvement.

CREATE PROCEDURE sp_tablesizes @.biggestfirst bit = 0
AS
-- sp_tablesizes
-- Dave Britten, 2007
-- Produces a list of all user and system tables in the current database,
-- optionally sorted by physical size. Also returns grand totals in a
-- second result set.

SET NOCOUNT ON
CREATE TABLE #tablelist (
Owner sysname,
Name sysname
)

CREATE TABLE #tabledata (
Name varchar(128),
Rows char(11),
reserved varchar(18),
Data varchar(18),
index_size varchar(18),
Unused varchar(18)
)

CREATE TABLE #sizedata (
rows int,
reserved int,
Data int,
index_size int,
Unused int
)

DECLARE @.dbname sysname
SET @.dbname = db_name()
DECLARE @.tablename sysname
DECLARE @.ownername sysname
DECLARE @.tablestring varchar(261)

INSERT INTO #tablelist
EXEC('SELECT sysusers.name, sysobjects.name FROM [' + @.dbname + ']..sysusers INNER JOIN [' + @.dbname + ']..sysobjects ON sysobjects.uid = sysusers.uid WHERE type IN (''U'', ''S'') AND sysobjects.name NOT LIKE ''tempdb..%''')

DECLARE tables CURSOR FAST_FORWARD FOR
SELECT Owner, Name FROM #tablelist
OPEN tables

FETCH NEXT FROM tables INTO @.ownername, @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tablestring = '[' + @.ownername + '].[' + @.tablename + ']'
INSERT INTO #tabledata
EXEC sp_spaceused @.tablestring
FETCH NEXT FROM tables INTO @.ownername, @.tablename
END

CLOSE tables
DEALLOCATE tables

INSERT INTO #sizedata (rows, reserved, data, index_size, unused)
SELECT
rows,
CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int),
CAST(LEFT(data, PATINDEX('% KB', data)) AS int),
CAST(LEFT(index_size, PATINDEX('% KB', index_size)) AS int),
CAST(LEFT(unused, PATINDEX('% KB', unused)) AS int)
FROM #tabledata

IF @.biggestfirst = 1
SELECT * FROM #tabledata ORDER BY CAST(LEFT(reserved, PATINDEX('% KB', reserved)) AS int) DESC
ELSE
SELECT * FROM #tabledata ORDER BY name ASC

SELECT
SUM(rows) [Total Rows],
STR(SUM(reserved)) + ' KB' [Total reserved],
STR(SUM(data)) + ' KB' [Total Data],
STR(SUM(index_size)) + ' KB' [Total index_size],
STR(SUM(unused)) + ' KB' [Total Unused]
FROM #sizedata

DROP TABLE #tablelist
DROP TABLE #tabledata
DROP TABLE #sizedata

GO|||

Hello davidbrit2 and all,

Thank you davidbrit2 for the replied. When I complied the stored procedure, I got the problem at database name as a variable.

e.g: DECLARE @.dbName AS VARCHAR(30)

SELECT @.dbName = 'Another Database'

SELECT name FROM [' + @.dbName + ']..sysobjects WHERE type = 'U' ORDER BY name

Then I received error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '' + @.dbName + '..sysobjects'.

Please advise.

|||

For an approximate row count of all user tables in a particular database in SQL Server 2000, use this:

SELECT OBJECT_NAME(si.[id]), si.[rows]

FROM dbo.sysindexes si

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want to include the tables' owners then you could use this:

SELECT su.[name] AS [Owner Name], so.[name] AS [Object Name], si.[rows]

FROM dbo.sysindexes si

INNER JOIN dbo.sysobjects so ON so.[id] = si.[id]

INNER JOIN dbo.sysusers su ON su.[uid] = so.[uid]

WHERE si.indid <= 1 --0 = a heap, 1 = a clustered index

AND OBJECTPROPERTY(si.[id], 'IsMSShipped') = 0

AND OBJECTPROPERTY(si.[id], 'IsUserTable') = 1

ORDER BY 1

If you want accurate row counts and can't be bothered creating and using a cursor then use this:

CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)

EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'

SELECT TableName, TableRowCount

FROM #tmpOutput

ORDER BY 1

DROP TABLE #tmpOutput

Chris

|||

Here is the script that can give you the number of counts in each table -

Declare @.testSql varchar(300)
Create Table #TempTabCount(TABLE_QUALIFIER sysname NULL,
TABLE_OWNER sysname NULL,
TABLE_NAME sysname NULL,
NON_UNIQUE smallint NULL,
INDEX_QUALIFIER sysname NULL,
INDEX_NAME sysname NULL,
TYPE smallint NULL,
SEQ_IN_INDEX smallint NULL,
COLUMN_NAME sysname NULL,
COLLATION char(1) NULL,
CARDINALITY int NULL,
PAGES int NULL,
FILTER_CONDITION varchar(128) NULL)

Declare TAB_CURSOR CURSOR for
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES

Open TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @.testSql
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #TempTabCount(TABLE_QUALIFIER,
TABLE_OWNER,
TABLE_NAME,
NON_UNIQUE,
INDEX_QUALIFIER,
INDEX_NAME,
TYPE,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
CARDINALITY,
PAGES,
FILTER_CONDITION)
exec sp_statistics @.testSql
FETCH NEXT FROM TAB_CURSOR INTO @.testSql
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR
Select TABLE_OWNER, TABLE_NAME, CARDINALITY from #TempTabCount

See if it solves your purpose. I tested it in SQL 2000 and working for me.

Enjoy!

Ash

|||Chris,

Ah-ha, it's sysindexes. I knew they had to be storing that information SOMEWHERE. ;)|||

Hi All,

Please find the query which will help to find the total number of records in all the tables in a SQL Server Database.

Select substring(obj.name, 1, 50) as Table_Name,
ind.rows as Number_of_Rows
from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id
where
obj.xtype = 'u'and ind.indid < 2
order by
obj.name

Regards
AKMEHTA

counting records in all non system database table

is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?

Try this:

EXECsp_msforeachtable'sp_spaceused "?"'

|||

You can use the TSQL below to get this information and some others. The results is similar to SP_SPACEUSED.

SELECT object_name(id) AS name,
rowcnt AS rows,
reserved * 8 AS reserved_kb,
dpages * 8 AS data_kb,
(sum(used) * 8) - (dpages * 8) AS index_size_kb,
(sum(reserved) * 8) - (sum(used) * 8) AS unused_kb
FROM sysindexes
WHERE indid IN (0,1) -- cluster e n?o cluster
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY rowcnt DESC

|||

Hi,

Try the following procedure

Code Snippet

ALTER procedure USP_CountAllTables
AS

DECLARE @.TblName Nvarchar(255)
DECLARE @.SQL nvarchar(1000)
DECLARE CntCursor CURSOR FOR
select Name from sysobjects where xtype='U'
Set @.SQL=NULL

create table #Temp
(tableName nvarchar(255),
Record_Count int
)

OPEN CntCursor
FETCH NEXT FROM CntCursor
into @.TblName
WHILE @.@.FETCH_STATUS = 0
BEGIN

SET @.SQL='Select ''' + @.TblName + ''',count(*) from '+ @.TblName

insert #Temp
execute sp_Executesql @.SQL

FETCH NEXT FROM CntCursor
into @.TblName
END
CLOSE CntCursor
DEALLOCATE CntCursor
select * from #Temp
Drop table #Temp

This will give u the record count for all the user tables.

But its gonna take some time so have patience.

RegarDs,

Jacx

counting records in a view

I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
Is this possible using a view?I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.

Is this possible using a view?

One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||How many rows are we talking about?

How about the DDL for the table|||One of the more frequently asked questions. Not doable in 7.0 or 2000. You can try something similar by creating a temp table (with an identity column); or you can do it with a table function.

Regards,

hmscott

Thanks for your reply. I was hoping i would be able to work around it without having to create a table, but it looks like that might just be the way do go afterall.

Thanks again,

Steve|||Yes, you can do this in any version of sql server as long as you have a unique column (or columns) that you can order by.
select MyTable.*,
(select count(*)
from MyTable SubTable
where SubTable.SortColumn <= MyTable.SortColumn) as OrdinalValue
from MyTable

Counting Records in a Stored Procedure

I am trying to count records in my stored procedure. Can someone please help me.

these are the two procedures I am using

Alter Procedure usp_rptQualityReport As

SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber

WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))

ORDER BY
tblFailureReasons.FailureReason

Alter Procedure usp_rptQualityReport2 As

exec usp_rtpQualityReport

SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,

(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel


FROM usp_rptQualityReport;What abotu this idea?

create procedure test
as
select * from sysobjects
go
create procedure test2
as
exec test
select @.@.rowcount
go
test2|||Are you counting number of lines in your stored procedure or number of records your stored procedure returns?


For number of lines check syscomments, for number of rows either use Profiler of alter your procedure, pass an OUTPUT parameter, and store the value of @.@.ROWCOUNT variable into it immediately after your query and before RETURN (yes, put a RETURN (0) statement at the end of your procedure)|||I am still trying to make this work and made some changes and created a view in SQL. But I still can't get them to count and then rank the records.

I am still having a problem counting the number of each type of Failure Reason.

Here is the Stored Procedure I am using:

Alter Procedure usp_rptQualityReport3 As

SELECT * FROM viewQualityReport

(SELECT COUNT(FailureReason)) AS groupingLevel

WHERE
(((viewQualityReport.FailureReason) <> N'NONE'))

ORDER BY
groupinglevel desc

counting records

Hi all,
I have a DTS package that gets information out of a Pervasive db and
drops it into SQL.
I need a count on how many distinct loan number are associated with
specific actions (ie Closing Date, Funded Date etc).
I have 2 tables: 1 contains a reference number and description (ie 170
= Closing Cost, 210 = Funded Date etc) and another table that contains
the reference number and loan number.
So I need a result set that says 20 loan have a closing date, 400 loans
have a funded date etc. There are over 590 reference numbers and over
1.5 million records.
Any help/advice would be immensely apprieciated!!
Thanks,
TonyTry,
select r.ref_desc, count(*) as cnt
from reference as r inner join loan as l
on r.ref_id = l.ref_id
group by r.ref_desc
go
AMB
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi Tony
You don't post ddl but you can do something like (untested):
SELECT [loan number], SUM(CASE WHEN [Closing Date] IS NOT NULL THEN 1 ELSE 0
END) AS NumClosed
SUM(CASE WHEN [Funded Date]IS NOT NULL THEN 1 ELSE 0 END) AS NumFunded
FROM #MyTable
GROUP BY [loan number]
John
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||On 26 Oct 2005 12:23:47 -0700, Tony wrote:

>Hi John,
>I thought of this but I have over 590 reference fields...don't feel
>like writing a sum/case for each one
Hi Tony,
Did you try Allejandro's suggestion?
If that doesn't help you, then please refer to www.aspfaq.com/5006 to
find out what extra information you need to post in order to help us
help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
590 columns in a table seems a large number. It is already causing you
problems by creating unwillingness try a solution!
You can use the information_schema.columns to create the sql for you.
e.g.
USE NORTHWIND
SELECT 'CASE WHEN ' + QUOTENAME (COLUMN_NAME) + ' IS NULL THEN 0 ELSE 1
END AS [SUM_' + COLUMN_NAME + '],'
from informatioN_schema.columns
where table_name = 'orders'
ORDER BY ORDINAL_POSITION
John
Tony wrote:
> Hi John,
> I thought of this but I have over 590 reference fields...don't feel
> like writing a sum/case for each one

Counting records

Hello,

I want to count some records in a table I have. My table is updated when someone posts information to an .asp page. Then, I use SSRS to create reports on this information in the table. I want to filter the results that the report shows based on how many records are in the table.

For instance:

Name last4 ID

John 2112 54432

John 3222 21223

John 7777 88888

John 3333 22222

John 3212 88722

Carol 2122 12111

Carol 5555 12111

Carol 3342 83635

Carol 1211 98363

Steve 2122 21331

James 2113 21123

Teresa 3223 21154

I want to filter the results shown in the report to records with > 3 occurances. So, the results for the above table would show only Carol and John.

Is there any way I can do this either in the SQL statement or in the reporting services statement?

Any help is appreciated.

Thanks

I think this ought to meet your needs

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

)

Just replace Some_Table with your table name.|||

Great. Thanks

I had something similar, but I had a few parts backwards. : )

So, if I wanted to add some more parameters should those go after the HAVING?

Like: HAVING COUNT (*) >3 AND Date BETWEEN @.startdate AND @.enddate

Thanks again.

|||Typically you would put the predicates that doesn't have any aggregate functions or GROUPING function in the WHERE clause. Logically the HAVING clause is seen as being evaluated after the GROUP BY clause has been evaluated so that the aggregate function results can be compared. This is different from the WHERE clause which can be evaluated at different stages (join conditions, table / index scans / seeks etc). So put the Date predicate in the WHERE clause and the COUNT(*) has to be in the HAVING clause.|||

So, if I understand correctly, it should be something like this:

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

) AND Date BETWEEN @.begindate AND @.enddate

Would that be the correct placement to include the date range in the WHERE clause?

Thanks again.

counting records

Hi,
I need to count records in a table to make server-side paging. COUNT(ID) is
rather expensive operation. Can I relay on a query returning number of rows
for a table based on rowcnt field in sysindexes table? I mean if this query
will return up-to-date number of rows.
thanks
PrzemoYou can rely on rowcnt in sysindexes to return a number that was accurate at
some point, but not necessarily at the time you query it.
Two things you can do:
1. Don't use COUNT(field_name), use COUNT(*) - using * allows sql server to
choose the smallest index to count
2. Use the NOLOCK hint on the table(s) being queried; while your query could
be inaccurate due to using NOLOCK, it's probably worth the performance
benefit in this case
"Przemo" wrote:

> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID) i
s
> rather expensive operation. Can I relay on a query returning number of row
s
> for a table based on rowcnt field in sysindexes table? I mean if this quer
y
> will return up-to-date number of rows.
> thanks
> Przemo|||If you run DBCC UPDATEUSAGE before the query.
HTH
Jerry
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID)
> is
> rather expensive operation. Can I relay on a query returning number of
> rows
> for a table based on rowcnt field in sysindexes table? I mean if this
> query
> will return up-to-date number of rows.
> thanks
> Przemo|||>> Can I relay on a query returning number of rows for a table based on
No, you cannot rely on the values in sysindexes. There are several known
instances where these values can be suspect. In some cases updating the
table statistics might help, but that is no way a guarantee for accuracy.
Anith|||When paging, is it really necessary to know beforehand how many total rows
are in the table or subset?
For example, would the following work?
select top 20 name, address, phone from customer where CustomerID >
@.PrevCustomerID
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID)
> is
> rather expensive operation. Can I relay on a query returning number of
> rows
> for a table based on rowcnt field in sysindexes table? I mean if this
> query
> will return up-to-date number of rows.
> thanks
> Przemo|||> select top 20 name, address, phone from customer
> where CustomerID > @.PrevCustomerID
You would need to ORDER BY CustomerID on that query.
"JT" wrote:

> When paging, is it really necessary to know beforehand how many total rows
> are in the table or subset?
> For example, would the following work?
> select top 20 name, address, phone from customer where CustomerID >
> @.PrevCustomerID
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
>
>|||I knew that, but it's a Friday afternoon, and I'm conserving keystrokes.
;-)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:D8EFDC52-4244-41AE-9078-2945ED2B74A3@.microsoft.com...
> You would need to ORDER BY CustomerID on that query.
>
> "JT" wrote:
>

Counting parent records and displaying Total?

We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

parameter # Calls
---- ---
desktop\pp 5
desktop\qq 6 {This is what I am getting at this stage}
desktop\tt 4
network\rr 9
network\gg 10
software\vv 3

This is what I would like to have:

parameter # Calls
---- ---
desktop 15
network 19
software 3

Please Help!select p.*, c.*, (p.amt+c.amt) as "Total Amt"
from parent p
INNER JOIN
(select id, sum(amt) as amt
from child c
group by id) c ON
p.id = c.id;|||Sorry, but I Forgot to mention that the second column is a count of the occurances in the 1st column. The query has to be run on only one field.

I am attaching my query:

SELECT workitem_category_tree_value,
count(*) as '# Work Items'

FROM dbo.workitem_detail_view hd1
WHERE hd1.[workitem_is_last] = 1
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%')

GROUP BY hd1.[workitem_category_tree_value]

Then this result is diplayed:

Desktop 8
Desktop\Administration 12
Desktop\Administration\Reset Password 42
Desktop\Administration\Unlock Account 30
Desktop\Notebook\LAN 1
Desktop\Notebook\LAN\Join to Domain 2
Desktop\Notebook\LAN\Network Connection 1
Desktop\Notebook\RAS 1
Desktop\Notebook\RAS\Configure 5

Counting no. of records

Hi

I need the ability to calculate the no. of records based on the no. of times a value in the sql report is given. For example based on a table shown below:

Ref No. First Name Surname 18 test test 18 test test 18 test test 19 test test 19 test test

I need to calulate the records returned on the ref no. I have managed to set page breaks based on a new ref no. with grouping and therefore the count will be displayed at the end of each of the records returned. As you can see there are three records returned for ref no. 18 and 2 for 19. How can I achieve this.

Many thanks in advance

If an aggregate of that column doesn't already exist then create one using something like

SUM(Ref No) AS 'Number of Records'

You could do this in the SQL or you can create it as a function which would look something like

=SUM(Ref No)

This field should be added to the footer column of the group.

Hope this helps

|||Hi,
I have created the table with the same information you have given.
I opened my Sql Server Reporting Services 2005, creating the new report.
First I placed the table control, placing the ref no, firstname, surname then i have created new group in the table called - "refno" ....in the group footer i have used the function ===> =CountRows("refno")

I think you know, how to set page break after group ends....

Do this....You will get the answer?
If you have any queries..let me know...

M Sivakumar|||Thanks for your reply Harley.

I have tried what you have suggested but =SUM is calculating the total for the Ref No. So if the ref no. 18 occurs twice, I get the value 36.. and so forth. Is this the right function? I thought this function would be to calculate the numbers mathematically. I know there is a count function but do not know the syntax of the expression. I need to understand how to use this function to calculate the no. of records returned for the ref no. e.g. if ref.no 18 occurs 10 times in the report, then below it should say total records = 10 and so forth for other ref no.s. I hope this makes more sense.

Cheers|||Hi Siva

First of all thanks for your help in trying to help, greatly appreciated.

Right, I do know how to create groups and set page breaks which I have implemented in my report and have the group footer below my rows. I tried using the countrows function but I think I am doing this wrong, the way I have added this is by using:

=CountRows(Fields!RefNo.Value)

the function with the fieldname in the brackets (is this the correct way?)
Do I need the speech marks also, I have tried all possibilites but it gives me an error:

The value expression for the textbox ‘textbox70’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Thank you again for your help

|||Sorry,

I meant use COUNT(RefNo) that should work, I always make that mistake then wonder why I get ridiculous answers :)

thanks|||

Hi,
No It is not the correct way. After creating the table, you just the create the new group and by default the group name will be "table1_Group1", you just modify the name into "refno" if you want to make it meaningful or you just leave it..
Now in the group footer...any of your cell you just add
"table1_Group1"
=CountRows("table1_Group1")

if u renamed into "refno"
=CountRows("refno")

I think, it will help your need.

|||I think I have found the solution to this. Siva first of all thank you for pointing me to the right direction.

The countrows function is indeed the right function for counting the rows returned. The way I have got this to work is by saying the following:

=CountRows("groupname") where groupname is the name of your group you have defined. It is imperative to include the speech marks.

I had a group set up as my criteria and have used this and seems to be working .

By the way, a good reference for this function and others supported by Microsoft use this website, its really good:

http://msdn2.microsoft.com/en-us/library/ms226986(en-US,SQL.90).aspx|||The above may be a better way of doing it but the way explained does work!|||Hi Harley

I'm sure it does work, not tried using it but I have the countrows function working so will stick with this one. In fact the reason why I feel the countrows function is better is due to the records returning actually fulfill my criteria which I specified in the group.

Cheers for your help too.

Counting Items in Categories

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

Like

Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)...

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName

Which Produces

Real Estate 12401 12
Extreme Sports 3 4

I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports 3 4
Cycling 5 0There is no such concept of an 'empty record'. If you were to describe to me an 'empty record', what would it be? In situations similar to what you describe, a record can have one of the following characteristics:

- Contain null values for one or more of its fields
- Not be returned with respect to some given criteria.

But there is no mention of an 'empty record' in relational theory or in any Database implementation. Looking at your query, I can't think of what it is you're actually trying to achieve, except in the case where a parent category may have no children, you need to return a result set similar to the following:

{ParentID, ChildCount}
ParentA, 0

When viewed in this way, the problem becomes a trivial LEFT JOIN query that will return all rows from set A irrespective of the contents of set B. In your example however, instead of returning the rows from Set B you will just return a count of the rows.

Select
SetA.columnA,
count(SetB.columnA)
from
SetA

left outer join SetB
on SetA.ColumnA = SetB.ColumnB

Remember: Simplification should be the goal of every developer. A paraphrased quote I once heard said: Perfection is reached not when you can no longer add to it, but when you can no longer take anything away.|||Ive found a short term solution will look at speeding it up when I have some spare time, and I have the live version working so it makes a bit more sense.

Start of December

counting distint records

i have an sql statement like this:

SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition

i simply want to modify it to give me the count, i tried this, but it doesn't work:

SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition

what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:

SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)