[thelist] SQL Chaos!!

[rudy]

> OK - I've used a collaboration of the answers I have received so far and
> come up with this :
>
> SELECT
>   Sum(FieldA),
>   Sum(FieldB),
>   Sub(FieldC),
>  (RIGHT('0' + cast(DatePart(dd,DateCreated) as varchar), 2) +'-'+
RIGHT('0'
> + cast(DatePart(mm,DateCreated) as varchar), 2) +'-'+
> cast(DatePart(yyyy,DateCreated) as varchar)) as GroupDate
> FROM InterestedForm, GeneralForm
> INNER JOIN GeneralForm ON InterestedForm.ID = GeneralForm.ID
> GROUP BY GroupDate

your GROUP BY columns do not match the non-aggregate columns in the SELECT

i mentioned this yesterday --

        martyn, do the view thing if you can, otherwise your GROUP BY
        will have to reflect individual column references

        for example, steve's would be

           group by DatePart(yyyy,bookDate)
                 , DatePart(mm,bookDate)
                 , DatePart(dd,bookDate)

actually, if you'd rather skip the view, that might be better, seeing as how
you are so new to sql

what are you using, access or sql server?  depending on which one you're
using, you can run the following as your query

   select Sum(FieldA) as SumFieldA
        , Sum(FieldB) as sumFieldB
        , Sub(FieldC) as SumFieldC
        , xxxxxxxxxxxxxxxxxxxxxx as GroupDate
     from InterestedForm
    inner
     join GeneralForm
       on InterestedForm.ID = GeneralForm.ID
   group
       by xxxxxxxxxxxxxxxxxxxxxx

if it's access, use

    Format(bookDate,'yyyy-mm-dd')

if it's sql server, use

    convert(char(10),bookDate,120)


rudy