sql sum prob... 1

[burnside]

hi,

this statement should return groups of departments by location.
also should count total deals for each department(countdeal) and sum deals that are within current month(countcurdeal) and forward of current month(countfwdeal)

at the moment i get this error

Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/dfasp2/dealgroup.asp, line 16
&"SUM( IF ( 100*Year(dealdeldate)+Month(dealdeldate) = 100*Year(curDate())+Month(curDate())), 1, 0 ) AS countcurdeal,"_

SQL = "SELECT TL.locid, TL.locname,TD.dealid, TD.userid, TD.dealdeldate, TDP.edeptdesc, TDP.edeptid,TU.userdeptid, TU.usersiteid,COUNT(TD.dealid) AS countdeal," 
&"SUM( IF ( 100*Year(dealdeldate)+Month(dealdeldate) = 100*Year(curDate())+Month(curDate())), 1, 0 ) AS countcurdeal,"_
&"SUM(IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate())), 1, 0 ) AS countfwdeal"_
& "FROM tbl_location AS TL, tbl_users AS TU, tbl_deal AS TD, tbl_dept AS TDP"_
	   & " WHERE tbl_users.usersiteid = tbl_location.locid "_
	   & "AND TU.userid = TD.userid"_ 
       &"AND TU.userdeptid = TDP.edeptid "_
      &"AND TU.usersiteid = TL.locid"_ 
	  & " GROUP BY TL.locid, TL.locname,TD.dealid, TD.userid, TD.dealdeldate, TDP.edeptdesc, TDP.edeptid,TU.userdeptid, TU.usersiteid "_
	  & " ORDER BY TL.locname, TDP.edeptid, TD.userid"
	  
Set RS = catConn.Execute( SQL )

 

[TonyGroves]

That's a compiler/interpreter error message, not a MySQL issue.

However, I notice that there's no underscore at the end of the first line. Could that have something to do with it?

 

[burnside]

thanks tony

am now getting this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '), 1, 0 ) AS countcurdeal,SUM(IF( 100*Year(dealdeldate)+Month(d
/dfasp2/dealgroup.asp, line 26

 

[TonyGroves]

If you format your SQL to make it easier to read, it would be easier to figure out problems like that.

You would then notice that the first SUM expression (and possibly the others) takes the form:
[tt]
SUM( IF( ... = ... ), 1, 0 )
[/tt]
which of course is wrong. Presumably it should be:
[tt]
SUM( IF( ... = ... , 1, 0 ) )
[/tt]

 

[burnside]

am now getting this error, is this because of the AS in FROM part (this is a new method and have not done it this way b4 - used to just put table names throughout)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'tbl_location AS TL, tbl_users AS TU, tbl_deal AS TD, tbl_dept A
/dfasp2/dealgroup.asp, line 26

SQL = "SELECT TL.locid, TL.locname,TD.dealid, TD.userid, TD.dealdeldate, TDP.edeptdesc, TDP.edeptid,TU.userdeptid, TU.usersiteid,COUNT(TD.dealid) AS countdeal, "_

&"SUM( IF ( 100*Year(dealdeldate)+Month(dealdeldate) = 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countcurdeal,"_

&"SUM( IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countfwdeal "_

&"FROM tbl_location AS TL, tbl_users AS TU, tbl_deal AS TD, tbl_dept AS TDP "_

&"WHERE tbl_users.usersiteid = tbl_location.locid "_

&"AND TU.userid = TD.userid "_ 
&"AND TU.userdeptid = TDP.edeptid "_
&"AND TU.usersiteid = TL.locid "_ 

&"GROUP BY TL.locid, TL.locname,TD.dealid, TD.userid, TD.dealdeldate, TDP.edeptdesc, TDP.edeptid,TU.userdeptid, TU.usersiteid "_

&"ORDER BY TL.locname, TDP.edeptid, TD.userid"

 

[burnside]

sorry got that sorted

 

[burnside]

thanks v much tony - am not quite gettin results i want but will look into it further

 

[burnside]

hi,

couple of things

1) I need to add tbl_stock AS TS but in my FROM clause i dont know how to do it as all other tables are INNER JOINS

2)in my sql i need to sum IF tbl_stock.stateid = 1
how do i put this in the existing add clause

3)i will also need a sum IF tbl_stock.stateid = 2 do i just add another sum clause for each stateid i need summing?


at the moment my sql looks like

SQL = "SELECT TL.locid, TL.locname,TD.dealid, TD.userid, TD.dealdeldate, TDP.saledept, TDP.saledeptid,TU.saledeptid, TU.usersiteid, TDB.barid, TDB.option, TD.saletypeid, "_

'this gets current month deals
&"SUM( IF ( 100*Year(dealdeldate)+Month(dealdeldate) = 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countcurdealuse, "_

'this gets forward month deals
&"SUM( IF( 100*Year(dealdeldate)+Month(dealdeldate) > 100*Year(curDate())+Month(curDate()), 1, 0 )) AS countfwdealuse "_

&"FROM(tbl_location AS TL INNER JOIN tbl_users AS TU ON TL.locid = TU.usersiteid) INNER JOIN tbl_deal AS TD ON TU.userid = TD.userid INNER JOIN tbl_salebar AS TDB ON TD.saletypeid = TDB.barid INNER JOIN tbl_saledept AS TDP ON TU.saledeptid = TDP.saledeptid " _

&"WHERE TU.usersiteid = TL.locid "_

&"AND TU.userid = TD.userid "_ 
&"AND TU.saledeptid = TDP.saledeptid "_
&"AND TD.saletypeid = TDB.barid "_
&"AND TU.usersiteid = TL.locid "_ 

&"GROUP BY TL.locid, TDB.barid, TD.userid "_

&"ORDER BY TL.locname, TDP.saledeptid, TDB.barid, TD.userid"