Query on million of rows

Question

Post reply

Query on million of rows

Little Nick

SSCrazy

Points: 2202
More actions
October 19, 2006 at 7:54 am

#173191

Hi,
I've these following table. This table containing million of rows. The sample of table and data shown as follow:-
DCC
DCC_ID | Description | CCC
-------------------------------
KLBF | London | England
KLDW | Manchester | England
KLSTL | Conventry | England
SLSJ | Chicago | US
SLSA | Las Vegas | US
CALLSIGN
Callsign| DCC
-----------------
BFD59 | KLBF
BFD78 | KLBF
BFD61 | KLBF
BFD55 | KLBF
BA10 | KLDW
BA12 | KLDW
C4H | KLSTL
AJ109 | SLSJ
AJ111 | SLSJ
AJ100 | SLSA
BA23 | SLSA
BFD57 | SLSA
LOGON_TYPE
Callsign | TIME_RECV
-------------------------------
BA10 | 7/9/2006 3:53:21 PM
BA10 | 7/9/2006 3:54:28 PM
C4H | 7/9/2006 4:53:21 PM
C4H | 7/9/2006 3:53:28 PM
BA10 | 7/9/2006 3:53:28 PM
BA10 | 7/9/2006 3:53:28 PM
AJ111 | 7/9/2006 3:53:28 PM
BA10 | 7/9/2006 3:53:28 PM
BA23 | 7/9/2006 3:53:28 PM
AJ111 | 7/9/2006 3:53:28 PM
BFD61 | 7/9/2006 3:53:28 PM
BFD78 | 7/9/2006 3:53:28 PM
BFD61 | 7/9/2006 3:53:28 PM
BFD59 | 7/9/2006 3:53:28 PM
BFD57 | 7/9/2006 3:53:28 PM
QUERY
CALLSIGN | TYPE | TIME_RCV
-----------------------------------------------
BA10 | JPJ | 7/9/2006 3:53:28 PM
BA10 | JPN | 7/9/2006 3:53:28 PM
C4H | JIM | 7/9/2006 3:53:28 PM
C4H | JPJ | 7/9/2006 3:53:28 PM
BA10 | JPJ | 7/9/2006 3:53:28 PM
BA10 | JIM | 7/9/2006 3:53:28 PM
AJ111 | JIM | 7/9/2006 3:53:28 PM
BA10 | JPJ | 7/9/2006 3:53:28 PM
BA23 | JIM | 7/9/2006 3:53:28 PM
AJ111 | JPN | 7/9/2006 3:53:28 PM
BFD61 | JIM | 7/9/2006 3:53:28 PM
BFD78 | JPJ | 7/9/2006 3:53:28 PM
BFD61 | JPJ | 7/9/2006 3:53:28 PM
BFD59 | JPN | 7/9/2006 3:53:28 PM
BFD57 | JIM | 7/9/2006 3:53:28 PM
I want to know, how many record (count) in LOGON_TYPE and QUERY by type.
If i want to group by Description and Description='London', the Expected output shown below:-
Description | LOGON_TYPE_COUNT | JPJ_Count | JPN_Count | JIM_Count
--------------------------------------------------------------------------------------
London | 3 | 2 | 1 | 1
How to query without using cursor or temp table to boost the performance?
Plz help me

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Try this:

select DCC.DCC_ID,DCC.[Description],

TYPECOUNT.LOGON_TYPE_COUNT,

sum(case when QUERY.TYPE = 'JPJ' then 1 else 0 end) as JPJ_Count,

sum(case when QUERY.TYPE = 'JPN' then 1 else 0 end) as JPN_Count,

sum(case when QUERY.TYPE = 'JIM' then 1 else 0 end) as JIM_Count

from DCC INNER JOIN CALLSIGN ON DCC.DCC_ID = CALLSIGN.DCC

LEFT OUTER JOIN

(

Select Count(1) as LOGON_TYPE_COUNT ,Callsign

From LOGON_TYPE Group by LOGON_TYPE

&nbsp TYPECOUNT ON CALLSIGN.Callsign = TYPECOUNT.Callsign

LEFT OUTER JOIN QUERY ON CALLSIGN.Callsign = QUERY.Callsign

Group by DCC.DCC_ID,DCC.[Description],TYPECOUNT.LOGON_TYPE_COUNT

Thanks

Sreejith

Michael Meierruth SSChampion Points: 10051 More actions · Answer 2

Your tables LOGON_TYPE and QUERY seem to be the same with the difference of one column (TYPE). Why this redundancy?