more union all issues 1

[timely]

I tried this and got that is wanted the actual names in the group by but then I put the names of the fields in the group by and it still did not like it.

error and code fallow
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.PMO_DSC' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'sub.KEY_CDE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.QTY_MAIL' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.QTY_SOLD' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.GRO_PCT' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.OTH_CPM' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.TOTL_CST' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.AVG_REV' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.GRO_REV' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.PMO_DSC' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'sub.KEY_CDE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.QTY_SOLD' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.QTY_SOLD' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'cpn.QTY_SOLD' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


select a1, a2, a3,a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14
from(
select
CPN.PMO_DSC a1,
SUB.key_cde a2,
CPN.QTY_MAIL a3,
QTY_SOLD a4,
GRO_PCT a5,
0 a6,0 a7,0 a8,0 a9,
CAST(CPN.OTH_CPM AS DECIMAL(8,2)) a10,
CAST(CPN.TOTL_CST AS DECIMAL(8,2)) a11,
CAST(CPN.AVG_REV AS DECIMAL(8,2)) a12,
CAST(CPN.GRO_REV AS DECIMAL(8,2)) a13,
AVG(SUB.TERM) a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND CTM_NBR = SRC_CTM AND
CPN.QTY_MAIL <> 0

union all

select
CPN.PMO_DSC a1,
SUB.key_cde a2,
0 a3,0 a4,0 a5,
COUNT(SUB.KEY_CDE) a6,
cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a7,
qty_sold - count(sub.key_cde) a8,
100 - cast(ROUND(count(sub.key_cde)/qty_sold * 100,2)as decimal(8,2)) a9,
0 a10,0 a11,0 a12,0 a13,0 a14
from cirsub_m sub
LEFT join cpnpmo_m cpn on sub.key_cde = cpn.cpn_pmo1
left JOIN ARPPDH_M PDH ON SUB.ORD_NBR = PDH.DBT_NBR AND
SUB.PUB_CDE = PDH.SRC_REF
where cpn.pub_cde = 'FCM' AND substring(key_cde,1,1) = 'D' AND SUB.BIL_STS = 'P' AND
CTM_NBR = SRC_CTM AND CPN.QTY_MAIL <> 0
) a
group by a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14

 

[r937]

the error messages are actually pretty straightforward

they flag non-aggregate columns in the SELECT list, which are invalid when you have an aggregate fucntion in the SELECT list, with no GROUP BY

in the first of the subqueries in the union, you have AVG(SUB.TERM) a14, and in the second of the subqueries, you have COUNT(SUB.KEY_CDE) a6

those are aggregate expressions, and without a GROUP BY in either of the subqueries, all non-aggregates in the SELECT lists are invalid

now, either you add GROUP BY clauses, consisting of all the non-aggregate columns in the SELECT lists, or else take out the aggregate expressions

finally, when you do the entire outer select and add a GROUP BY for all those columns, the only benefit this gives you is that it removes duplicates

you can achieve that by eliminating the outer select and changing UNION ALL to UNION

rudy

http://r937.com/

SQL Consulting

 

[timely]

I got it to work once I also did the group by in inside the 2 queries buy it did not join the info together the way I wanted it to. I need if the first two fields are the same they are combined. should I just do to views and then combined the info that way?

 

[timely]

I did not mean to but two views

 

[r937]

&quot;if the first two fields are the same they are combined&quot;?

you'll need to give some example rows for that

rudy

http://r937.com/

SQL Consulting

 

[timely]

This was the results and I need that since the first two fields are the amd that they combine but they did not using the outer select.


FCM DIRECT MAIL APRIL 2002 DFCFL241 41148 19 0.04 0 0 0 0 393.29 16183.1 20.37 387.08 12
FCM DIRECT MAIL APRIL 2002 DFCFL241 0 0 0 12 63.16 7 36.84 0 0 0 0 0
FCM DIRECT MAIL APRIL 2002 DFCGE241 12404 11 0.08 0 0 0 0 290.38 3601.87 19.95 219.45 12
FCM DIRECT MAIL APRIL 2002 DFCGE241 0 0 0 4 36.36 7 63.64 0 0 0 0 0

 

[r937]

okay, i see

well, you will have to go back to the idea of doing an outer select, but this time, use a GROUP BY on only the first two fields, and use SUM() on the rest



rudy

http://r937.com/

SQL Consulting

 

[timely]

Thanks, that took care of it.