how to write query without using group by clause

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 write query without using group by clause

Author

Topic

gayathri3088
Starting Member

2 Posts

Posted - 2010-01-20 : 08:08:34

There are 2 columns in table
1.employee number(primery key)
2.employee type

How to find number of employees for each employee type
without using group by clause?

gayathri

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 13:11:47

"How to find number of employees for each employee type without using group by clause?"

You can't (well, you can, but only for a SINGLE employee-type, as divyaram described)

Tell us what you are trying to do, and whyt GROUP BY is a problem, and I'm sure someone here can help with a solution

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 06:19:16

Here's one way (on 2005 and up)

But yes -- why would you do something like this when you could use a simple group by?


SELECT
    e.[employeeType]
    ec.[number]
FROM
    employee e
    CROSS APPLY (
        SELECT
            COUNT([employeeNumber]) AS [number]
        FROM
            employee e2
        WHERE
            e2.[employeeType] = e.[employeeType]
        )
        ec

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 06:24:31

Or even this using a sub-query


SELECT
    e.[employeeType]
    , ( SELECT COUNT(e2.[employeeNumber]) AS [number] FROM employee e2 WHERE e2.[employeeType] = e.[employeeType] )
FROM
    employee e

This will *work* on most any version sql server but it is horrible. GROUP BY (and even CROSS APPLY) are a lot, lot faster as they are set based. This is a horrible row operation which should be avoided at all costs.

I'm interested to know the reason for no GROUP BY.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 06:36:21

Hehehehe ... slow day T.C.?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 06:50:30

How could you tell!

I find my post count here is inversely proportional to my workload!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gayathri3088
Starting Member

2 Posts

Posted - 2010-01-21 : 09:24:55

Thank you Transact Charlie...
It is very useful for me.

gayathri

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 10:18:12

quote:
Originally posted by gayathri3088

Thank you Transact Charlie...
It is very useful for me.

gayathri

No -- don't go!!

Can you please tell us why you can't use the GROUP BY?

It is the best method by a long, long way.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-01-21 : 10:58:25

Homework?

CODO ERGO SUM

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 11:06:01

IT's a particularly ass backward class if it is!

How to do this in the worst possible way? Discuss

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 11:15:39

It would make an interesting interview question. Correct answer would be WTF would I do that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 04:45:28

quote:
Originally posted by gayathri3088

There are 2 columns in table
1.employee number(primery key)
2.employee type

How to find number of employees for each employee type
without using group by clause?

gayathri

Will this work?

select
distinct
[employee type],
count(employeenumber) over (partition by [employee type])
from your_table

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:48:04

quote:
Originally posted by madhivanan

quote:
Originally posted by gayathri3088

There are 2 columns in table
1.employee number(primery key)
2.employee type

How to find number of employees for each employee type
without using group by clause?

gayathri

Will this work?

select
distinct
[employee type],
count(employeenumber) over (partition by [employee type])
from your_table

Madhivanan

Failing to plan is Planning to fail

it will only thing is that you'll get total count of employees in a type repeated with each employee record

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 05:12:28

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by gayathri3088

There are 2 columns in table
1.employee number(primery key)
2.employee type

How to find number of employees for each employee type
without using group by clause?

gayathri

Will this work?

select
distinct
[employee type],
count(employeenumber) over (partition by [employee type])
from your_table

Madhivanan

Failing to plan is Planning to fail

it will only thing is that you'll get total count of employees in a type repeated with each employee record

Did you mean count(employeenumber) over (partition by [employee type]) would always give same value?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:18:42

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by gayathri3088

There are 2 columns in table
1.employee number(primery key)
2.employee type

How to find number of employees for each employee type
without using group by clause?

gayathri

Will this work?

select
distinct
[employee type],
count(employeenumber) over (partition by [employee type])
from your_table

Madhivanan

Failing to plan is Planning to fail

it will only thing is that you'll get total count of employees in a type repeated with each employee record

Did you mean count(employeenumber) over (partition by [employee type]) would always give same value?

Madhivanan

Failing to plan is Planning to fail

yup for the employees belonging to same type, it will return same count value along with each of the individual employee details


ex:

EmpNo  EmpName            employee type
1      Madhivanan          IT
2      Visakh              IT
3      Raju                Finance
4      Sekhar              Admin
5      Shyam               Finance
6      Mahesh              Finance

the result will be like

EmpNo  EmpName            employee type     Count
1      Madhivanan          IT                 2
2      Visakh              IT                 2
3      Raju                Finance            3
4      Sekhar              Admin              1
5      Shyam               Finance            3
6      Mahesh              Finance            3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 05:22:59

Do you execute this and see what you get?



declare @t table(EmpNo  int, EmpName varchar(30),           [employee type] varchar(20))
insert into @t
select 1,      'Madhivanan',          'IT' union all
select 2,      'Visakh',              'IT' union all
select 3,      'Raju',                'Finance' union all
select 4,      'Sekhar',              'Admin' union all
select 5,      'Shyam',               'Finance' union all
select 6,      'Mahesh',              'Finance' 


select 
distinct
[employee type],
count(EmpNo) over (partition by [employee type]) 
from @t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:25:21

quote:

Originally posted by madhivanan

Do you execute this and see what you get?



declare @t table(EmpNo  int, EmpName varchar(30),           [employee type] varchar(20))
insert into @t
select 1,      'Madhivanan',          'IT' union all
select 2,      'Visakh',              'IT' union all
select 3,      'Raju',                'Finance' union all
select 4,      'Sekhar',              'Admin' union all
select 5,      'Shyam',               'Finance' union all
select 6,      'Mahesh',              'Finance' 


select 
distinct
[employee type],
count(EmpNo) over (partition by [employee type]) 
from @t

Madhivanan

Failing to plan is Planning to fail

Ah my bad . I didnt see the distinct .
ok that makes sense

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:26:44

I really feel this like an interview question

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 05:29:13

quote:
Originally posted by visakh16

I really feel this like an interview question

There is no doubt on that
Some clever interviewer might have asked this question

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 05:31:44

<<
Ah my bad . I didnt see the distinct .
ok that makes sense
>>

Thats the effect of too many posts

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:34:44

quote:
Originally posted by madhivanan

<<
Ah my bad . I didnt see the distinct .
ok that makes sense
>>

Thats the effect of too many posts

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources