How to get count of all records along with records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get count of all records along with records

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 03:33:17

Hi all,
I have one query like
select col1,col2,col3...........col15
from table1
where......

which gives me 1000 rows

Can it be possible to get this count (1000) along with select query above?
like
select col1,col2,col3...........col15,count()
from table1
where......

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 03:54:42

is it possible to qrite it i single query?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 04:13:17

[code]SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YOURS TABLE'[/code]

Hope can help...but advise to wait pros with confirmation...

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 04:17:39

To waterduck
but this will give me count of that table only
i need count along with all selected field

is it possible?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 04:24:59

i think you need pros to help you that...>"<

Hope can help...but advise to wait pros with confirmation...

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 04:31:53

is there sample SP for this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 04:40:30

are you using SQL 2000 or 2005/2008 ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 04:44:16

select col1,col2,col3...........col15, count(*) OVER (PARTITION BY (SELECT 0))
from table1
where......

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 04:58:49

...i thought OP wanna count column instead of count row...

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 06:31:16

quote:
Originally posted by Peso

select col1,col2,col3...........col15, count(*) OVER (PARTITION BY (SELECT 0))
from table1
where......

N 56°04'39.26"
E 12°55'05.63"

or

select col1,col2,col3...........col15, count(*) OVER ()
from table1
where......

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 06:34:44

If you are not using SQL Server 2005/2008

declare @count int
set @count=(select count(*) from ........)
select col1,col2,..........,@count from
............

Madhivanan

Failing to plan is Planning to fail

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 10:46:36

SELECT
[SAP Order Cost].[KC_SAP_ORDER_COST_ACCTI_CHR] "ACCTINGIND",
[SAP Order Cost].[KC_SAP_ORDER_COST_BUDGE_CHR] "BUDGETTYPE",
[SAP Order Cost].[KC_SAP_ORDER_COST_CALYEAR_CHR] "CALENDARYEAR",
[SAP Order Cost].[KC_SAP_ORDER_COST_CLIEN_CHR] "Client",
[SAP Order Cost].[KC_SAP_ORDER_COST_COST_AMOUN_NBR] "Cost Amount",
[SAP Order Cost].[KC_SAP_ORDER_COST_COST_CATEG_CHR] "Cost Category",
[SAP Order Cost].[KC_SAP_ORDER_COST_COST_TYPE_NBR] "Cost Type",
[SAP Order Cost].[KC_SAP_ORDER_COST_CURRE_CHR] "Currency",
[SAP Order Cost].[KC_SAP_ORDER_COST_DEBIT_CHR] "DEBITTYPE",
[SAP Order Cost].[KC_SAP_ORDER_COST_MAINT_PLANT_CHR] "Maintenance Plant",
[SAP Order Cost].[KC_SAP_ORDER_COST_OBJNU_CHR] "OBJNUM",
[SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] "Order Number",
[SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_STATU_CHR] "Order Status",
[SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_TYPE_CHR] "Order Type",
[SAP Order Cost].[KC_SAP_ORDER_COST_PERIO_NBR] "PERIODBLOCK",
[SAP Order Cost].[KC_SAP_ORDER_COST_PLANN_PLANT_CHR] "Planning Plant",
[SAP Order Cost].[KC_SAP_ORDER_COST_VARR_NBR] "VAR/RES_CAT",
[SAP Order Cost].[KC_SAP_ORDER_COST_VERSI_CHR] "VERSION"
,Count(*) "RecordCount" over ()

FROM [SAP Order Cost] JOIN [SAP Order] ON [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] = [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]

WHERE [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] <> [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]
-----------------------------------------------------
I tried above but fails

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-13 : 10:51:11

so what is the error that it returns?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-13 : 10:53:42

Count(*) "RecordCount" over ()
Count(*) over () as "RecordCount"

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 10:54:04

Count(*) "RecordCount" over ()

should be

Count(*) over () as RecordCount

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 10:54:47

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-13 : 10:57:54

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-13 : 10:59:59

quote:
Originally posted by webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

This time you were fast

Madhivanan

Failing to plan is Planning to fail

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 11:14:09

SELECT [SAP Order Cost].[KC_SAP_ORDER_COST_ACCTI_CHR] "ACCTINGIND", [SAP Order Cost].[KC_SAP_ORDER_COST_BUDGE_CHR] "BUDGETTYPE", [SAP Order Cost].[KC_SAP_ORDER_COST_CALYEAR_CHR] "CALENDARYEAR", [SAP Order Cost].[KC_SAP_ORDER_COST_CLIEN_CHR] "Client", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_AMOUN_NBR] "Cost Amount", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_CATEG_CHR] "Cost Category", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_TYPE_NBR] "Cost Type", [SAP Order Cost].[KC_SAP_ORDER_COST_CURRE_CHR] "Currency", [SAP Order Cost].[KC_SAP_ORDER_COST_DEBIT_CHR] "DEBITTYPE", [SAP Order Cost].[KC_SAP_ORDER_COST_MAINT_PLANT_CHR] "Maintenance Plant", [SAP Order Cost].[KC_SAP_ORDER_COST_OBJNU_CHR] "OBJNUM", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] "Order Number", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_STATU_CHR] "Order Status", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_TYPE_CHR] "Order Type", [SAP Order Cost].[KC_SAP_ORDER_COST_PERIO_NBR] "PERIODBLOCK", [SAP Order Cost].[KC_SAP_ORDER_COST_PLANN_PLANT_CHR] "Planning Plant", [SAP Order Cost].[KC_SAP_ORDER_COST_VARR_NBR] "VAR/RES_CAT", [SAP Order Cost].[KC_SAP_ORDER_COST_VERSI_CHR] "VERSION"
,Count(*) over () "RecCount"
FROM [SAP Order Cost] JOIN [SAP Order] ON [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] = [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]

WHERE [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] <> [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]
-----------------------------------------
still it's showing prob

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 11:32:38

I'm sorry......
below query works........
------------------------------------------
SELECT [SAP Order Cost].[KC_SAP_ORDER_COST_ACCTI_CHR] "ACCTINGIND", [SAP Order Cost].[KC_SAP_ORDER_COST_BUDGE_CHR] "BUDGETTYPE", [SAP Order Cost].[KC_SAP_ORDER_COST_CALYEAR_CHR] "CALENDARYEAR", [SAP Order Cost].[KC_SAP_ORDER_COST_CLIEN_CHR] "Client", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_AMOUN_NBR] "Cost Amount", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_CATEG_CHR] "Cost Category", [SAP Order Cost].[KC_SAP_ORDER_COST_COST_TYPE_NBR] "Cost Type", [SAP Order Cost].[KC_SAP_ORDER_COST_CURRE_CHR] "Currency", [SAP Order Cost].[KC_SAP_ORDER_COST_DEBIT_CHR] "DEBITTYPE", [SAP Order Cost].[KC_SAP_ORDER_COST_MAINT_PLANT_CHR] "Maintenance Plant", [SAP Order Cost].[KC_SAP_ORDER_COST_OBJNU_CHR] "OBJNUM", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] "Order Number", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_STATU_CHR] "Order Status", [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_TYPE_CHR] "Order Type", [SAP Order Cost].[KC_SAP_ORDER_COST_PERIO_NBR] "PERIODBLOCK", [SAP Order Cost].[KC_SAP_ORDER_COST_PLANN_PLANT_CHR] "Planning Plant", [SAP Order Cost].[KC_SAP_ORDER_COST_VARR_NBR] "VAR/RES_CAT", [SAP Order Cost].[KC_SAP_ORDER_COST_VERSI_CHR] "VERSION"
,Count(*) over () "RecCount"
FROM [SAP Order Cost] JOIN [SAP Order] ON [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] = [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]

WHERE [SAP Order Cost].[KC_SAP_ORDER_COST_ORDER_NUMBE_CHR] <> [SAP Order].[KC_SAP_ORDER_ORDER_NUMBE_CHR]

-----------------------------------------
but it shows prob as-->

Could not allocate space for object '<temporary system object: 422264067260416>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-07-13 : 11:32:39

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources