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

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.

CodePudding user response：

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?

CodePudding user response：

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
;

CodePudding user response：

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;