SQLTeam.com | Weblogs | Forums

Adding a count to a view


#1

Hi There,

I have a view that pulls from a number of tables and it returns the correct records.
I am now trying to have a count added to the view and it is complaining that fields need to be in a "group by".
This makes things very hard as I have alias column names etc.
Is there a way to get over this?
I don't really need to count, what I need to do is for every row, look in a table and if there is a record with an ItemId = to the main tables ItemId then put a 1 in the column else put a 0 In the column

Any ideas on that?

Thanks for any help.

Best Regards,


#2
SELECT <whatever_columns>,
    CASE WHEN EXISTS(SELECT 1 FROM lookup_table lt WHERE lt.itemid = mt.itemid) 
         THEN 1 ELSE 0 END AS column_name,
    <more_columns>
FROM main_table mt
...