Group By not working no clue why (1 Viewer)

[geoffcodd]

Hi,

I have the following SQL

SELECT 
'MNC (NASP)' AS Type
, NASP_ID
, Region_1
, '/' AS Country
, Sum(Total) As Total 
FROM TMA.tblG2100_TMA_Target_Complete
WHERE ((Type)='MNC (Accounts)')
GROUP BY Type, NASP_ID, Region_1, Country

I get the following results, and as you can see it doesn't group correctly and I don't know why

MNC (NASP) 10AFQH APAC / 0
MNC (NASP) 10AFQH EMEA / 0
MNC (NASP) 10AFQH EMEA / 0
MNC (NASP) 10AFQH US / 768867

Any one have any ideas why

Thanks in advance

 

[SQL_Hell]

Hi there,

Same as your other stored procedure problem, you cannot group by type and country because they are not actually columns they are aliases.

Try a derived table query, like this:

SELECT 
'MNC (NASP)' AS [Type]
, b.NASP_ID
, b.Region_1
, '/' AS Country
, b.Total 
FROM
(
select , NASP_ID
, Region_1
, Sum(Total) As Total
FROM TMA.tblG2100_TMA_Target_Complete
WHERE ((Type)='MNC (Accounts)')
GROUP BY Type, NASP_ID, Region_1
)b