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

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-13 : 22:52:49

hi,
this sounds simple, but ive to idea how ..
1. how to count all records we found when there's group by?
SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
ORDER BY ItemID

2. how to print rows number for each record? like the Expr1 column
Expr1 ItemID CustomLotNo Ownership
1 A A01 INT1
2 C GPB01 JAB2MY

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 01:56:34


select *
from
(
SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
) a 
cross join
(
    select record_cnt = count(*)
    from   tblItemDetail
)b
ORDER BY ItemID

2.

Are you using SQL 2005 ? use the ROW_NUMBER() function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 01:58:55

SELECT ItemID, CustomLotNo, Ownership, count(*)
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
ORDER BY ItemID

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-14 : 02:37:58

nope im using (from the about)

Microsoft SQL Enterprise Manager
Microsoft Corporation
Version: 8.0

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

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-14 : 02:43:45

peso : that one count the group, not the total result
khtan : if i just run the query, it returns 3076 result serach, but when i trid to run your count query i expect it will return the same, but it's not.. why it count 27062? imm not sure why..

thanks guys

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 02:49:35

you mean


SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership

will return 3076 records
and with the cross join


select *
from
(
SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
) a 
cross join
(
    select record_cnt = count(*)
    from   tblItemDetail
)b

it will return 27062 records ?

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-14 : 03:34:11

oopsss sorry, both return 3076 rows.. but with the cross join the record_cnt=27062

what is that record_cnt? ithought it suppose count the records total rows??

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 04:46:56

record_cnt is the total records in tblItemDetail


    select record_cnt = count(*)
    from   tblItemDetail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:27:57

SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
ORDER BY ItemID

SELECT @@ROWCOUNT

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-15 : 02:28:59

Pesao!! yep that was exactly what i want.. thanks..
khtan.. i thought the record count could be same as 3076(after we group by).. nvm.. ill use peso's.. tq ..

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 02:41:55

quote:
Originally posted by maya_zakry

Pesao!! yep that was exactly what i want.. thanks..
khtan.. i thought the record count could be same as 3076(after we group by).. nvm.. ill use peso's.. tq ..

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

Now i understand what you want.

What you wanted is the @@rowcount gives you number of records from that query. The record_cnt give number of records in that table. Which is totally different from the one you wanted.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-15 : 02:52:10

oh.. now i understand what is actually the record_cnt..number of records b4 we do the group by isit?? ooo...

yepp.. sometimes i have problem to put into words what i need to accomplish due to my poor english :P

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 03:02:29

Not a problem. English is not my first language also.

That's why we normally will ask for sample data and the expected result. Easier to understand and less chances to get things wrong when you have a specification

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources