Count record from different table

Question

Post reply

Count record from different table

Sharul Nizam

Ten Centuries

Points: 1302
More actions
October 17, 2006 at 2:09 am

#94796

Hi, i've these following table
LOGON_TYPE
ID | DATETIME
-----------------
KL202 | 821am
KL202 | 300pm
KL202 | 430pm
KL101 | 230pm
KL101 | 700pm
QUERY
ID | TYPE | DATETIME
-----------------------
KL202 | JPJ | 455pm
KL202 | JPJ | 515pm
KL202 | JPN | 230pm
KL202 | JIM | 450pm
KL202 | JIM | 1123am
KL101 | JPJ | 230pm
KL101 | JPJ | 300pm
KL101 | JIM | 400pm
I need to know, 'How many record for each ID in this 2 tables'.
How to query to generate the output below?
ID | LOGON_TYPE_RECORD | JPJ_RECORD | JPN_RECORD | JIM_RECORD
-----------------------------------------------------------------------------
KL202 | 3 | 2 | 1 | 2
KL101 | 2 | 2 | 0 | 1
Plz help me .

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

-- Test Data

DECLARE @t TABLE

(

TID CHAR(5) COLLATE DATABASE_DEFAULT NOT NULL

,TYPE CHAR(3) COLLATE DATABASE_DEFAULT NOT NULL

)

INSERT @t

SELECT 'KL202', 'JPJ' UNION ALL

SELECT 'KL202', 'JPN' UNION ALL

SELECT 'KL202', 'JIM' UNION ALL

SELECT 'KL101', 'JPJ' UNION ALL

SELECT 'KL101', 'JIM'

-- The Query

SELECT TID

,COUNT(DISTINCT TYPE) AS LOGON_TYPE_RECORD

,COUNT(CASE WHEN TYPE = 'JPJ' THEN 1 END) AS JPJ_RECORD

,COUNT(CASE WHEN TYPE = 'JPN' THEN 1 END) AS JPN_RECORD

,COUNT(CASE WHEN TYPE = 'JIM' THEN 1 END) AS JIM_RECORD

FROM @t

GROUP BY TID

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 2

Hi,

I did'nt understand the statement,

INSERT @t

SELECT 'KL202', 'JPJ' UNION ALL

SELECT 'KL202', 'JPN' UNION ALL

SELECT 'KL202', 'JIM' UNION ALL

SELECT 'KL101', 'JPJ' UNION ALL

SELECT 'KL101', 'JIM'

if so many ID in the table, there's a lot of SELECT 'KL202', 'JPJ' .. need to write?

Can you give another alternatives?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This code is just to have a demo table and data to show how the query works.

You don't actually need to reinsert any data in the table .

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 4

How i can avoid using table variable for boost the performance?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Is this related to this question or is this a new question all together?

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

@t is a TEST table.

1. Take the query: