[thelist] SQL Chaos!!

Martyn Haigh martyn.haigh at virgincosmetics.com
Wed Jan 29 06:36:02 CST 2003


Darren,

Thanks for that - I'm a bit of a novice at SQL )c:  (trying to learn!)

Using your code - I added a GROUP BY as I need it grouped by the date.
Unfortunately it doesn't like the GroupDate in the Group By clause.

SELECT
Sum(i.OtherEnq),
Sum(i.CatReq),
Sum(i.KnowMore),
CONVERT(varchar(10), g.DateCreated, 105) AS GroupDate
FROM InterestedForm as i
INNER JOIN GeneralForm as g ON i.ID = g.ID
GROUP BY GroupDate

Do you have any ideas?

Thank you for your help.
(also like your domain name - I'm thinking about getting business cards with
web bitch printed on them!)

Martyn Haigh

Site Developer
The Virgin Cosmetics Company
City Fields, Chichester, West Sussex PO20 2FP
tel: 01243 622282


-----Original Message-----
From: darren [mailto:darren at web-bitch.co.uk]
Sent: 29 January 2003 12:13
To: thelist at lists.evolt.org
Subject: Re[2]: [thelist] SQL Chaos!!

On Wednesday, January 29, 2003 at 08:53, Martyn Haigh wrote:

MH> SELECT
MH>   Sum(FieldA),
MH>   Sum(FieldB),
MH>   Sub(FieldC),
MH>  (RIGHT('0' + cast(DatePart(dd,DateCreated) as varchar), 2) +'-'+
RIGHT('0'
MH> + cast(DatePart(mm,DateCreated) as varchar), 2) +'-'+
MH> cast(DatePart(yyyy,DateCreated) as varchar)) as GroupDate
MH> FROM InterestedForm, GeneralForm
MH> INNER JOIN GeneralForm ON InterestedForm.ID = GeneralForm.ID
MH> GROUP BY GroupDate

MH> But I'm experiencing the error of "tables / functions generalForm and
MH> generalForm have the same exposed name"

yeah, you're adding the same table into the query twice.  i don't think
you'd need the generalForm table in your where clause, just in the join.

you're also not indicating which table you are pulling the columns from,
if you have the same column name in both tables, you will also get an
error.

i haven't been following this, but if you're using sql server, can't you
use convert and a date style to get the date format you want??  something
like:

   convert(varchar(10), DateCreated, 105) as groupDate

should give a date in dd-mm-yyyy format.

so as an example (and assuming sql server!) - if you were getting fieldA,
B and C from the InterestedForm table and CreatedDate from the
GeneralForm table you would have something like:

   SELECT
      SUM(i.FieldA),
      SUM(i.FieldB),
      SUM(i.FieldC),
      CONVERT(varchar(10), g.DateCreated, 105) as GroupDate
   FROM
      InterestedForm as i
   INNER JOIN GeneralForm as g
      ON i.ID = g.ID

hth,

darren

--
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !


DISCLAIMER: The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be taken
in reliance on it, is prohibited and may be unlawful.





More information about the thelist mailing list