Count row - SQL Server Forums

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

Count row

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-25 : 23:26:03

hi all,

how to count record rows found, no matter how is my sql statement structure is..
count(*) if i select all, but how to count result if there;s Group by clause, when i dont have unique key to represent each row? thanks in advance

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 23:32:51

to count no of records in table

select count(*) from tbl

with group by clause

select somecol, count(*) from tbl group by somecol

Is this what you want ?

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-26 : 00:44:19

ermm.. i dun think so, because that will return many rows with total..
i want it to count how many rows found by some sql query... like

SELECT CustomLotNo, StorageID,
FROM tblItemDetail d
WHERE (ItemID = 'GCL243V1-ARH')
GROUP BY CustomLotNo, StorageID

will list the found rows, but how to return the total rows it found.

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 01:03:02

[code]
Option 1 :
SELECT CustomLotNo, StorageID
FROM tblItemDetail d
WHERE (ItemID = 'GCL243V1-ARH')
GROUP BY CustomLotNo, StorageID

select @@rowcount

Option 2 :
select count(*)
from
(
SELECT CustomLotNo, StorageID
FROM tblItemDetail d
WHERE (ItemID = 'GCL243V1-ARH')
GROUP BY CustomLotNo, StorageID
) a

Option 3 :
SELECT CustomLotNo, StorageID,count(*)
FROM tblItemDetail d
WHERE (ItemID = 'GCL243V1-ARH')
GROUP BY CustomLotNo, StorageID
with rollup
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 01:04:52

Option 4 :
Do it in your front end application
[(c) Copyright Madhivanan]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 02:22:24

Before we get sued by Madhivanan, don't forget the copyright

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 02:23:51

Thanks for the reminder

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-26 : 03:58:19

thank you so much guys..

For sure option 2 works the way i want it to..unfortunately option 1 and 3 cannot, it didnt returns total row, it returns the rows itself.. it's just that i wonder if there's special function to perform this... apparently got to do like the old method.. (option2)

~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 04:07:13

[code]
Option 1 :
SELECT CustomLotNo, StorageID
FROM tblItemDetail d
WHERE (ItemID = 'GCL243V1-ARH')
GROUP BY CustomLotNo, StorageID

select @@rowcount
[/code]

the total rows is return from the @@rowcount

Option 3 will required a bit more work as the total count is in the result sets.

Any not Option 4 ?

(Do you know I have to pay the copyright fee to quote that

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources