By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,758 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,758 IT Pros & Developers. It's quick & easy.

Help with a simple query

P: 14
Hi guys,

I'm running MSDE 2000 A on Win XP

I've got a database with several tables, all of the tables display data in query manager.

I wrote this simple query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select
  3. Feed_3PT_CC.LIST_NO,
  4. Feed_3PT_CC.LIST_PRICE,
  5. Feed_3PT_CC.STREET_NO,
  6. Feed_3PT_CC.STREET_NAME,
  7. Feed_3PT_CC.PROP_TYPE,
  8. Feed_3PT_CC.NO_BEDROOMS,
  9. sum (Feed_3PT_CC.NO_FULL_BATHS+ Feed_3PT_CC.NO_HALF_BATHS) Number_of_Bathrooms ,
  10. Feed_3PT_Open_Houses.Start_Date
  11. From
  12.       Feed_3PT_CC,
  13.       Feed_3PT_Open_Houses
  14. where Feed_3PT_CC.LIST_NO= Feed_3PT_Open_Houses.LIST_NO
  15. group by Feed_3PT_CC.LIST_NO, Feed_3PT_Open_Houses.Start_Date
  16.  
  17.  
Looks like there are no mistakes in it (im a newbie with MS SQL though)

Here is what EMC SQL manager returns when I run this query:

Expand|Select|Wrap|Line Numbers
  1. Column 'Feed_3PT_CC.LIST_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  2. Column 'Feed_3PT_CC.LIST_PRICE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  3. Column 'Feed_3PT_CC.STREET_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  4. Column 'Feed_3PT_CC.STREET_NAME' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  5. Column 'Feed_3PT_CC.PROP_TYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  6. Column 'Feed_3PT_CC.NO_BEDROOMS' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  7. Column 'Feed_3PT_Open_Houses.Start_Date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  8.  
Any ideas of how to make this query work?

I need an output of all those simple fields plus NO_FULL_BATH and NO_HALF_BATH summed up to be shown in one table sorted by LIST_NO and Start_Date

Thanks so much!

I really appreciate your help!

Oleg
Jun 20 '07 #1
Share this Question
Share on Google+
3 Replies


tifoso
P: 41
Take the bloody sum out if all you need is those 2 fields added, sum is for a different use RTFM a bit more

select f1,f2, ... , f2 +f3 , ... blah blah blah
Jun 21 '07 #2

P: 14
Take the bloody sum out if all you need is those 2 fields added, sum is for a different use RTFM a bit more

select f1,f2, ... , f2 +f3 , ... blah blah blah

How do I sum them up then?
Jun 21 '07 #3

P: 99
Hi guys,

I'm running MSDE 2000 A on Win XP

I've got a database with several tables, all of the tables display data in query manager.

I wrote this simple query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select
  3. Feed_3PT_CC.LIST_NO,
  4. Feed_3PT_CC.LIST_PRICE,
  5. Feed_3PT_CC.STREET_NO,
  6. Feed_3PT_CC.STREET_NAME,
  7. Feed_3PT_CC.PROP_TYPE,
  8. Feed_3PT_CC.NO_BEDROOMS,
  9. sum (Feed_3PT_CC.NO_FULL_BATHS+ Feed_3PT_CC.NO_HALF_BATHS) Number_of_Bathrooms ,
  10. Feed_3PT_Open_Houses.Start_Date
  11. From
  12.       Feed_3PT_CC,
  13.       Feed_3PT_Open_Houses
  14. where Feed_3PT_CC.LIST_NO= Feed_3PT_Open_Houses.LIST_NO
  15. group by Feed_3PT_CC.LIST_NO, Feed_3PT_Open_Houses.Start_Date
  16.  
  17.  
Looks like there are no mistakes in it (im a newbie with MS SQL though)

Here is what EMC SQL manager returns when I run this query:

Expand|Select|Wrap|Line Numbers
  1. Column 'Feed_3PT_CC.LIST_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  2. Column 'Feed_3PT_CC.LIST_PRICE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  3. Column 'Feed_3PT_CC.STREET_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  4. Column 'Feed_3PT_CC.STREET_NAME' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  5. Column 'Feed_3PT_CC.PROP_TYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  6. Column 'Feed_3PT_CC.NO_BEDROOMS' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  7. Column 'Feed_3PT_Open_Houses.Start_Date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
  8.  
Any ideas of how to make this query work?

I need an output of all those simple fields plus NO_FULL_BATH and NO_HALF_BATH summed up to be shown in one table sorted by LIST_NO and Start_Date

Thanks so much!

I really appreciate your help!

Oleg
If you use group by clause you have to give all the fields in group by clause


group by Feed_3PT_CC.LIST_NO, Feed_3PT_Open_Houses.Start_Date,Feed_3PT_CC.LIST_P RICE,Feed_3PT_CC.STREET_NO,
Feed_3PT_CC.STREET_NAME,Feed_3PT_CC.PROP_TYPE,Feed _3PT_CC.NO_BEDROOMS
Jun 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.