select count help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select count help

Author

Topic

azna
Starting Member

6 Posts

Posted - 2011-03-24 : 03:54:28

Hi,

Need ur help on this, I have two sets of queries:

Query 1:
SELECT DISTINCT TOP 5 COUNT(B.WN) AS TOTALWO,
SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1) AS XXX
FROM WORKORDER B
WHERE
AND B.WC = 'WORKORDER'
AND B.STATUS NOT IN ('CAN','CLOSE')
AND B.WT NOT IN ('PM')
AND B.AUO = 0
GROUP BY SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1)
ORDER BY TOTALWO DESC

Query 2:
SELECT DISTINCT TOP 5 COUNT(C.WN) AS TOTALWO1,
SUBSTRING(C.DESCRIPTION,1,CHARINDEX(' ', REPLACE(C.DESCRIPTION, '-', ' ')) - 1) AS XXX
FROM WORKORDER C
WHERE
AND C.WC = 'WORKORDER'
AND C.STATUS NOT IN ('CAN','CLOSE')
AND C.WT NOT IN ('PM')
AND C.AUO = 1
GROUP BY SUBSTRING(C.DESCRIPTION,1,CHARINDEX(' ', REPLACE(C.DESCRIPTION, '-', ' ')) - 1)
ORDER BY TOTALWO1 DESC

The difference is only at the AUO column. And the value of XXX is getting from Query 1. But i dont know how to merge both.

I need to get the result as example below:

TOTALWO TOTALWO1 XXX
12 4 PLANTA
10 3 PLANTB
9 2 PLANTC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-24 : 05:11:44

use

AUO IN (0, 1)

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 06:22:53

How about:

SELECT DISTINCT TOP 5
COUNT(B.WN) AS TOTALWO,
sum(case when C.AUO=0 then 1 else 0 end) AS TOTALWO_0,
sum(case when C.AUO=1 then 1 else 0 end) AS TOTALWO_1,
SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1) AS XXX
FROM WORKORDER B
WHERE
AND B.WC = 'WORKORDER'
AND B.STATUS NOT IN ('CAN','CLOSE')
AND B.WT NOT IN ('PM')
AND B.AUO in (0,1)
GROUP BY SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1)
ORDER BY TOTALWO DESC

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

azna
Starting Member

6 Posts

Posted - 2011-03-24 : 11:17:38

Do I need to do a join coz there is C.AUO in the SQL below?

SELECT DISTINCT TOP 5
COUNT(B.WN) AS TOTALWO,
sum(case when C.AUO=0 then 1 else 0 end) AS TOTALWO_0,
sum(case when C.AUO=1 then 1 else 0 end) AS TOTALWO_1,
SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1) AS XXX
FROM WORKORDER B
WHERE
AND B.WC = 'WORKORDER'
AND B.STATUS NOT IN ('CAN','CLOSE')
AND B.WT NOT IN ('PM')
AND B.AUO in (0,1)
GROUP BY SUBSTRING(B.DESCRIPTION,1,CHARINDEX(' ', REPLACE(B.DESCRIPTION, '-', ' ')) - 1)
ORDER BY TOTALWO DESC

I think I can't use AUO in (0,1) because I need the result in query 1 and use the output value of the substring of the Query 1 to get the count of WN in Query 2. The intended result is to get the same substring value but different value of count in Query 1 and Query 2 and it differentiated by AUO column.

So the result that I need is something like:

count_of_query1 substring_value count_of_query2
20 AHP 15
18 WTPR 10
9 GTOPM 8

Please help..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 13:23:53

Did you try the script I gave you?

You don't need 2 queries...

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

azna
Starting Member

6 Posts

Posted - 2011-03-24 : 22:17:20

Yes, I have tried..but still couldn't get the intended result..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-24 : 22:20:52

best is for you to post some sample data from your WORKORDER table and show your intended result

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

azna
Starting Member

6 Posts

Posted - 2011-03-24 : 23:17:44

The result that I get is as per below:

TOTALWO_0 TOTALWO_1 XXX
39 0 AHS
24 0 FUEL
21 0 STEAM
18 0 DRAUGHT
16 0 CHS

I should have the result as:

TOTALWO_0 TOTALWO_1 XXX
39 1 AHS
24 2 FUEL
21 8 STEAM
18 2 DRAUGHT
16 0 CHS

When I remove the select TOP 5 (I just simply select the value of XXX as above result), it returns 2 lines of each of the XXX value:

TOTALWO_0 TOTALWO_1 XXX
39 0 AHS
0 1 AHS
18 0 DRAUGHT
0 2 DRAUGHT
21 0 STEAM
0 8 STEAM

I think more or less the result is there but it's just the matter on how to pick up the value of TOTALWO_1 <> 0, am I right?

azna
Starting Member

6 Posts

Posted - 2011-03-25 : 02:49:08

Oh I just get the result correct by modifiying the Order By instead of putting TOTALWO to TOTALWO_0.

Thanks guys!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources