Generate top ten records best seller.

Applications Grid / Reports
, ,
#1

hi, i need a report with top ten productos best seller, i have a SQL for this report.
But scriptcase has a conflict with COUNT function, i don not know how can i do it in SC.

This SQL works fine!

select suc_nmbre AS Sucursal, pro_nmbrecrto as Producto, sum(kar_cntdad) as Cantidad
from kar_krdex kar, pro_prdcto pro, suc_scrsal suc
where suc.neg_cdgo = 1
and kar.suc_cdgo = suc.suc_cdgo
and kar.pro_cdgo = pro.pro_cdgo
and kar_fcha > DATE_SUB(CURDATE(),INTERVAL 30 DAY)
group by 1, 2
order by 3 desc limit 10;

How can i do it in SC.
Someone can help me?

#2

Hi alvagar, i have done that kind of report, and i don’t have any trouble. just put your SQL Sentence and select the fields
and its done.

what database are you using?

#3

Hello,

If for some reason, you are unable to create a grid with tihs SQL statement, you can create a View, and then create the grid selecting the View.

regards,
Bernhard Bernsmann

#4

Hi, jj and bartho, i am using mysql, When i create this report i pasted last SQL statement and i got a error message :

Se produjo un error al acceder a la base de datos
Can’t group on 'count()’
select count() from kar_krdex , pro_prdcto , suc_scrsal where suc_scrsal.neg_cdgo = 1 and kar_krdex.suc_cdgo = suc_scrsal.suc_cdgo and kar_krdex.pro_cdgo = pro_prdcto.pro_cdgo and kar_fcha > DATE_SUB(CURDATE(),INTERVAL 30 DAY) group by 1, 2.

I did a test (on ONE TABLE) with other SQl and it works, I think that when the sql is with more than one table it fail.

This SQL works.

select suc_cdgo, pro_cdgo, sum(kar_cntdad)
from kar_krdex
group by suc_cdgo, pro_cdgo
order by 3 desc;

#5

Hello,

Then create a view on this statement:

select suc_nmbre AS Sucursal, pro_nmbrecrto as Producto, sum(kar_cntdad) as Cantidad
from kar_krdex kar, pro_prdcto pro, suc_scrsal suc
where suc.neg_cdgo = 1
and kar.suc_cdgo = suc.suc_cdgo
and kar.pro_cdgo = pro.pro_cdgo
and kar_fcha > DATE_SUB(CURDATE(),INTERVAL 30 DAY)
group by 1, 2
order by 3 desc limit 10;

And create a new grid on the view.

SELECT * FROM NewView

regards,
Bernhard Bernsmann

#6

Very Strange…

What version of SC did you use?
What Version of MySQL?