[thelist] SQL Chaos!!
rudy
r937 at interlog.com
Wed Jan 29 06:40:34 CST 2003
> 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
More information about the thelist
mailing list