Group By and Percentage in List

Hi,
you can construct your query with group by clause and use this query on the Edit SQL query tab.

Here is a sample:

SELECT field_name_1,...,field_name_n, (Hours/sum(Hours))*100 as percentage_hours

Hi,
Unfortunately when ASPRunnner adds a 'WHERE' clause it does not add it correctly when there is a 'GROUP BY'. The 'WHERE' gets added after the 'GROUP BY' thus making the SQL query incorrect.
E.g. 'select [blah] from [blahblah] where [blah] = [blah] group by [blah]
Please can greater parsing of the SQL query take place to enable greater functionality in the SQL query.
One of the fantastic features of the 'Edit Query' process is that (when possible) a very complex query incorporating many tables can be used which then allows the client to edit one of the underlying tables.
I routinely create a view in SQL server and then copy it into the 'Edit Query' step in ASPRunner.
From this I have noticed two problems with the way ASPRunner deals with the SQL Query.

1. If a 'TOP (n)' or 'TOP (n) PERCENT' is present then this is not recognised and another one is added to get the view required to display as requested. e.g. SQL query becomes:
   
   'select top 20 TOP 20 from [blah] where [blah] = [blah]' <-- No good..
   
2. If a 'GROUP BY' is used then - see above.
   I suspect there may be others as there only seems to be minimal parsing of the query.
   Kind Regards.

Hi,
please see my answers below:

1. yes, ASPRunner adds TOP cluase to the query.
   
   If you want to remove it in your application you can post your project to the Demo Account, send me link to your pages and I'll help you. Demo Account button is on the last tab in the ASPRunnerpro.
   
2. GROUP BY clause works correct in the last ASPRunnerPro 4.1. Please download and install the latest update of ASPRunnerPro:
   
   http://www.asprunner.com/files/asprunnerpro-setup.exe

Jane,

1. It is not me adding the TOP in the query, when making complex views using the GUI in SQL Server Management Studio it adds 'TOP (100) PERCENT', I don't know why but if this is removed the query does not work. It would be better if AspRunner checked for the prescence of a TOP clause in the query and removed that in favour of the one AspRunner adds.
   
2. I already have build 225, if there is a later build I do not know where to get it. 225 does not handle the 'GROUP BY' correctly when used with the _list page of a project that was dependant on the login credentials.
   My query in 'edit query' is:
   SELECT dbo.Orderheads.SuppCode, dbo.Orderheads.SuppName AS ManuName, COUNT(DISTINCT dbo.Orderheads.OrdNo) AS Orders,
   
   SUM(dbo.Orderlines.PartQty) AS Qty, dbo.Orderheads.SuppName
   
   FROM dbo.Orderheads INNER JOIN
   
   dbo.Orderlines ON dbo.Orderheads.OrdNo = dbo.Orderlines.OrdNo INNER JOIN
   
   dbo.PartsSources ON dbo.Orderlines.PartCode = dbo.PartsSources.PartCode AND dbo.Orderheads.SuppCode = dbo.PartsSources.PartSupp
   
   WHERE (dbo.Orderheads.OrdComp IN('O', 'P'))
   
   GROUP BY dbo.Orderheads.SuppCode, dbo.Orderheads.SuppName
   I get an error [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.
   The query has changed to:
   
   SELECT dbo.Orderheads.SuppCode, dbo.Orderheads.SuppName AS ManuName, COUNT(DISTINCT dbo.Orderheads.OrdNo) AS Orders,
   
   SUM(dbo.Orderlines.PartQty) AS Qty, dbo.Orderheads.SuppName
   
   FROM dbo.Orderheads INNER JOIN
   
   dbo.Orderlines ON dbo.Orderheads.OrdNo = dbo.Orderlines.OrdNo INNER JOIN
   
   dbo.PartsSources ON dbo.Orderlines.PartCode = dbo.PartsSources.PartCode AND dbo.Orderheads.SuppCode = dbo.PartsSources.PartSupp
   
   WHERE ( (dbo.Orderheads.OrdComp IN('O', 'P'))
   
   GROUP BY dbo.Orderheads.SuppCode, dbo.Orderheads.SuppName) and (dbo.Orderheads.SuppCode='HKR001')
   The syntax error being caused by the '(' that has been added. The WHERE clause has not taken account of he 'GROUP BY'
   Am I missing something?
   Kind regards.

Hi,
I could really do with an answer to this query....
Kind regards