Announcement

Collapse
No announcement yet.

Generate top ten records best seller.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generate top ten records best seller.

    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?

    Comment


    • #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

      Comment


      • #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;

        Comment


        • #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

          Comment


          • #6
            Very Strange.....

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

            Comment

            Working...
            X