CODEWITHSUNDEEP
aggregate-functionsnetezza
1Hunter1983
1Hunter1983

Reputation: 7

Count returns null with Insert Into, but it works when I just use select in Netezza

I'm trying to insert data into a table and one of my columns is coming back null. The columns I sum are working fine, but the one I count is returning null with the insert into command followed by select. When I eliminate insert into, and just run my select statement I get the result I need. What am I doing wrong?

`

INSERT INTO DAILY_TOTALS(TIME_FRAME,
    VIDEO,
    DATA,
    VOICE,
    PREV_VIDEO,
    PREV_DATA,
    PREV_VOICE,
    VIDEO_REVENUE,
    INTERNET_REVENUE,
    PHONE_REVENUE,
    OCC_REVENUE,
    TOTAL_REVENUE,
    ACCOUNTS)
SELECT
D.TIME_FRAME,
D.VIDEO,
D.DATA,
CASE WHEN D.VOICE ='N' THEN 'N' ELSE 'Y' END AS VOICE,
D.PREV_VIDEO,
D.PREV_DATA,
CASE WHEN D.PREV_VOICE = 'N' THEN 'N' ELSE 'Y' END AS PREV_VOICE,
SUM(D.VIDEO_REVENUE) AS VIDEO_REVENUE,
SUM(D.INTERNET_REVENUE) AS INTERNET_REVENUE,
SUM(D.OCC_REVENUE) AS OCC_REVENUE,
SUM(D.TOTAL_REVENUE) AS TOTAL_REVENUE,
COUNT(D.ACCOUNT_NUMBER) AS ACCOUNTS
FROM DAILY D
WHERE  D.TIME_FRAME = CURRENT_DATE -1
GROUP BY 
D.TIME_FRAME,
D.VIDEO,
D.DATA,
D.VOICE,
D.PREV_VIDEO,
D.PREV_DATA,
D.PREV_VOICE`

Upvotes: 0

Views: 55

Answers (1)

Mark F
Mark F

Reputation: 291

Aren't you missing 1 column?

The INSERT statement mentions 13 columns ...but the SELECT only selects 12 columns.

So "COUNT(D.ACCOUNT_NUMBER) AS ACCOUNTS" is being inserted into the TOTAL_REVENUE column. And the ACCOUNTS column in your table is NULL because you haven't specified anything to go in there.

Upvotes: 1

Related Questions