CODEWITHSUNDEEP
sqlteradata
Bamboo
Bamboo

Reputation: 11

Teradata: selected non-aggregate values must be part of the associated group

This is my SQL statement:

SELECT 
    A.username,
    A.logdate,
    SUM(A.AMPCPUTIME) AS cpu,
    SUM(A.TOTALIOCOUNT) AS totalIO,
    COUNT(A.QUERYID) AS qrycount,
    (cpu / b.total_CPU) * 100 AS cpu_threshold_percentage,
    (totalIO / b.total_IOUsage) * 100 AS io_threshold_percentage,
    (qrycount / b.QRY_count) * 100 AS qry_threshold_percentage
FROM 
    pdcrinfo.DBQLOGTBL_HST A
INNER JOIN
    VT_BASELINE b ON A.username = b.username
WHERE 
    A.LOGDATE = DATE-1 
    AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
GROUP BY
    A.username, A.logdate;

I don't know which column I'm missing out in the group by section. I only have two non aggregated columns in the select section.

Upvotes: 1

Views: 582

Answers (3)

dnoeth
dnoeth

Reputation: 60472

The columns from b are not in GROUP BY, either add them or apply an aggregate function, e.g. MAX(b.total_CPU).

But aggregate before join should be more efficient:

select a.*,
   (cpu / b.total_CPU) * 100 AS cpu_threshold_percentage,
   (totalIO / b.total_IOUsage) * 100 AS io_threshold_percentage,
   (qrycount / b.QRY_count) * 100 AS qry_threshold_percentage
from
 (
    SELECT 
        A.username,
        A.logdate,
        SUM(A.AMPCPUTIME) AS cpu,
        SUM(A.TOTALIOCOUNT) AS totalIO,
        COUNT(A.QUERYID) AS qrycount,
    FROM 
        pdcrinfo.DBQLOGTBL_HST A
    WHERE 
        A.LOGDATE = DATE-1 
        AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
    GROUP BY
        A.username, A.logdate
 ) as a
INNER JOIN VT_BASELINE b 
ON A.username = b.username
;

Upvotes: 1

Francisco Guiroy
Francisco Guiroy

Reputation: 17

Teradata doesn't allow you to use the aggregations that are inside the same query. You need to recalculate them like this:

SELECT 
    A.username,
    A.logdate,
    SUM(A.AMPCPUTIME) AS cpu,
    SUM(A.TOTALIOCOUNT) AS totalIO,
    COUNT(A.QUERYID) AS qrycount,
    (SUM(A.AMPCPUTIME)/ b.total_CPU) * 100 AS cpu_threshold_percentage,
    (SUM(A.TOTALIOCOUNT)/ b.total_IOUsage) * 100 AS io_threshold_percentage,
    (COUNT(A.QUERYID)/ b.QRY_count) * 100 AS qry_threshold_percentage
FROM 
    pdcrinfo.DBQLOGTBL_HST A
INNER JOIN
    VT_BASELINE b ON A.username = b.username
WHERE 
    A.LOGDATE = DATE-1 
    AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
GROUP BY
    A.username, A.logdate;

Upvotes: 0

user3408245
user3408245

Reputation:

Your aggregates are CPU, TotalIO and QryCount

SELECT 
    A.username,
    A.logdate,
    SUM(A.AMPCPUTIME) AS cpu,
    SUM(A.TOTALIOCOUNT) AS totalIO,
    COUNT(A.QUERYID) AS qrycount,
    (cpu / **b.total_CPU**) * 100 AS cpu_threshold_percentage,
    (totalIO / **b.total_IOUsage**) * 100 AS io_threshold_percentage,
    (qrycount / **b.QRY_count**) * 100 AS qry_threshold_percentage
FROM 
    pdcrinfo.DBQLOGTBL_HST A
INNER JOIN
    VT_BASELINE b ON A.username = b.username
WHERE 
    A.LOGDATE = DATE-1 
    AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
GROUP BY
    A.username, A.logdate;

The bolded fields (Note see ** around the fields) need to be in the group by or aggregated in this current query. CPU, TotalIO, and QryCount should work because Teradata allows in-line aggregates to be reference below in the select statement

See total_CPU, total_IOUsage, QRY_count

I don't know your data, but could you put them in the select like you did the previous aggregates? Using, Count, Max, Min, or Sum? Then reference them in your formulas?

Upvotes: 1

Related Questions