Query on million of rows

wkm1925 · October 19th, 2006, 09:09 AM

rgerald · October 19th, 2006, 09:52 AM

I'm looking at the table structures (DCC, CALLSIGN and LOGON_TYPE). I can't figure out where you get the TYPE column in the QUERY query - what table is it in?

Once that is determined, perhaps a pivot table will solve the problem.

Rand

wkm1925 · October 19th, 2006, 10:15 AM

can you show me how pivot table will help solve this problem?

rgerald · October 19th, 2006, 11:12 AM

If you apply a pivit table to the following:

Code:

SELECT Description, Type, Count(*) FROM (
    SELECT Description, Callsign, Type, Time_Rcv 
    FROM QUERY
    INNER JOIN CALLSIGN
    ON QUERY.CALLSIGN = CALLSIGN.CALLSIGN
    INNER JOIN DCC
    ON CALLSIGN.DCC = DCC.DCC_ID
) pp

I would like to see the results of the above query, if you can send me a sample.

Oh by the way, can you guarantee the uniqueness of Description or do you need to concatenate Description and CCC to get an unique descriptor.

Rand

rgerald · October 19th, 2006, 01:48 PM

Here is your answer:

Code:

SELECT J2.Description, LOGON_TYPE_COUNT, JPJ_COUNT, JPN_COUNT, JIM_COUNT FROM (
    SELECT Description, Count(*) LOGON_TYPE_COUNT FROM (
        SELECT Description, CALLSIGN.Callsign, Time_Recv 
        FROM LOGON_TYPE
        INNER JOIN CALLSIGN
        ON LOGON_TYPE.CALLSIGN = CALLSIGN.CALLSIGN
        INNER JOIN DCC
        ON CALLSIGN.DCC = DCC.DCC_ID
    ) pp
    GROUP BY Description
) J1
INNER JOIN (
    SELECT
        Description
        , SUM(CASE Type WHEN 'JPJ' THEN pt.RecCount ELSE 0 END) AS JPJ_COUNT
        , SUM(CASE Type WHEN 'JPN' THEN pt.RecCount ELSE 0 END) AS JPN_COUNT
        , SUM(CASE Type WHEN 'JIM' THEN pt.RecCount ELSE 0 END) AS JIM_COUNT
    FROM (
        SELECT Description, Type, Count(*) RecCount FROM (
            SELECT Description, CALLSIGN.Callsign, Type, Time_Rcv 
            FROM QUERY
            INNER JOIN CALLSIGN
            ON QUERY.CALLSIGN = CALLSIGN.CALLSIGN
            INNER JOIN DCC
            ON CALLSIGN.DCC = DCC.DCC_ID
        ) pp
        GROUP BY Description, Type
    ) pt
    GROUP BY Description
) J2
ON    J1.Description = J2.Description

Try it out.

Good luck!

Rand

wkm1925 · October 20th, 2006, 06:49 AM

tq rgerald. it's helping me a lot.

tq.

rgerald · October 23rd, 2006, 09:30 AM

One more thing.

If the QUERY.TIME_RCV data is the same as LOGON_TYPE.TIME_RECV and the QUERY.CALLSIGN is the same as LOGON_TYPE.CALLSIGN, then the answer I sent you can be simplified even further (and I assume made to run faster) since references to the LOGON_TYPE table can be eliminated from the answer.

Rand