SQL Server Performance Forum – Threads Archive
Query on million of rows
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?
Somthing like this ..?? select
t1.Name,t2.LOGON_TYPE_COUNT
sum(
case when t3.type = ‘JPJ’ then 1 else 0 end
) as JPJ_Count,
sum(
case when t3.type = ‘JPN’ then 1 else 0 end
) as JPN_Count
sum(
case when t3.type = ‘JIM’ then 1 else 0 end
) as JIM_Count from DCC t1 join
(
Select Count(1) LOGON_TYPE_COUNT ,Callsign From LOGON_TYPE Group by LOGON_TYPE
)t2
on t1.Callsign = t2.Callsign
TABLE3 t3
on t1.Callsign = t3.Callsign
Where t1.Description=’London’
Group by t1.[Name],t2.LOGON_TYPE_COUNT
Chirag http://chirikworld.blogspot.com/
This query give an error. Can u help me to debug the error?
what error you got? Chirag http://chirikworld.blogspot.com/
from DCC t1 join (Select Count(1) LOGON_TYPE_COUNT ,Callsign From LOGON_TYPE Group by LOGON_TYPE )t2on t1.Callsign = t2.CallsignTABLE3 t3on t1.Callsign = t3.CallsignWhere t1.Description=’London’Group by t1.[Name],t2.LOGON_TYPE_COUNT 1st error is
From my table, DCC do not have column Callsign.
Opps Copy paste..
select
t1.Name,t2.LOGON_TYPE_COUNT
sum(
case when t3.type = ‘JPJ’ then 1 else 0 end
) as JPJ_Count,
sum(
case when t3.type = ‘JPN’ then 1 else 0 end
) as JPN_Count
sum(
case when t3.type = ‘JIM’ then 1 else 0 end
) as JIM_Count from DCC t1 join
(
Select Count(1) LOGON_TYPE_COUNT ,Callsign From LOGON_TYPE Group by LOGON_TYPE
)t2
on t1.DCC_ID= t2.Callsign
TABLE3 t3
on t1.DCC_ID= t3.Callsign
Where t1.Description=’London’
Group by t1.[Name],t2.LOGON_TYPE_COUNT
Chirag http://chirikworld.blogspot.com/
Still got error,
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ‘TABLE3’. there’s no TABLES3
replace table3 with the tablename of the last table which you posted in your first table. you havent mentioned any name over there… Chirag http://chirikworld.blogspot.com/
]]>