Announcement

Collapse
No announcement yet.

Grid report , select sum by year

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

  • Grid report , select sum by year

    hello all

    i have a table like this
    Table Order_History

    ID
    Order_id
    order_count
    order_year_date


    I want to get : group by Order_id and sum(order_count) like this
    Order_id 2011 2012 2013 2014

    To understand better please see attached photo
    Untitled.png

    also if possible column year to be titled dynamically when the user chose starting year

    thanks in advanced
    Last edited by walid; 04-04-2015, 03:21 PM. Reason: solved

  • #2
    okay, i found a way to get this but not dynamic

    Code:
    SELECT  order_id, 
            SUM(CASE WHEN YEAR(order_date) = 2011 THEN order_count ELSE 0 END) AS '2011',
            SUM(CASE WHEN YEAR(order_date) = 2012 THEN order_count ELSE 0 END) AS '2012',
            SUM(CASE WHEN YEAR(order_date) = 2013 THEN order_count ELSE 0 END) AS '2013'
    
    FROM    order_history
    GROUP BY 
            order_id
    what i need is to get years dynamic, any clue ?

    Comment


    • #3
      use [GLOBALS]

      Regards

      Comment


      • #4
        Originally posted by kafecadm View Post
        use [GLOBALS]

        Regards
        Dear Kafecadm
        i did like this :
        i start the Grid application as search and i create a new field with name {starting_year}and i set variable [YEAR] = {starting_year}
        i changed my code to:

        Code:
        SELECT  order_id, 
                SUM(CASE WHEN YEAR(order_date) = [YEAR] THEN order_count ELSE 0 END) AS [YEAR],
                SUM(CASE WHEN YEAR(order_date) = [YEAR] +1 THEN order_count ELSE 0 END) AS [YEAR] +1,
                SUM(CASE WHEN YEAR(order_date) = [YEAR] +2 THEN order_count ELSE 0 END) AS [YEAR] +2
        
        FROM    order_history
        GROUP BY 
                order_id
        when run after chose starting date i get error, i believe my code is wrong, can you check it?

        Thanks in advanced

        Comment


        • #5
          To give you a better answer I'll need you to provide the error message, tho, I think your problem is actually the way you building the query, those +1 and +2 are messing you up.

          instead of that in the onapplicationinit of your report write some code like this.

          PHP Code:
          [YEAR1]= [YEAR] + 1;
          [
          YEAR2]= [YEAR] + 2
          Then modify your query to be something like this:

          PHP Code:

          SELECT  order_id

                  
          SUM(CASE WHEN YEAR(order_date) = [YEARTHEN order_count ELSE 0 END) AS [YEAR],
                  
          SUM(CASE WHEN YEAR(order_date) = [YEAR1THEN order_count ELSE 0 END) AS [YEAR1],
                  
          SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS [YEAR2]

          FROM    order_history
          GROUP BY 
                  order_id 
          Regards

          Comment


          • #6
            Originally posted by kafecadm View Post
            To give you a better answer I'll need you to provide the error message, tho, I think your problem is actually the way you building the query, those +1 and +2 are messing you up.

            instead of that in the onapplicationinit of your report write some code like this.

            PHP Code:
            [YEAR1]= [YEAR] + 1;
            [
            YEAR2]= [YEAR] + 2
            Then modify your query to be something like this:

            PHP Code:

            SELECT  order_id

                    
            SUM(CASE WHEN YEAR(order_date) = [YEARTHEN order_count ELSE 0 END) AS [YEAR],
                    
            SUM(CASE WHEN YEAR(order_date) = [YEAR1THEN order_count ELSE 0 END) AS [YEAR1],
                    
            SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS [YEAR2]

            FROM    order_history
            GROUP BY 
                    order_id 
            Regards
            i tried to do like above i still get error

            Invalid argument supplied for foreach() | Script: C:\Program Files\NetMake\v8\wwwroot\scriptcase\devel\compat\n m_select_atualiza.php
            and Error while accessing the database:

            Also when i rewrite my SQL like it was before it keep giving me the same error "Error while accessing the database", it will not run until i delete the grid and make another 1 ( is it bug )?

            regards

            Comment


            • #7
              oh damn my bad:

              PHP Code:
              SELECT  order_id,  
                      
              SUM(CASE WHEN YEAR(order_date) = [YEARTHEN order_count ELSE 0 END) AS '[YEAR]'
                      
              SUM(CASE WHEN YEAR(order_date) = [YEAR1THEN order_count ELSE 0 END) AS '[YEAR1]'
                      
              SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS '[YEAR2]' 

              FROM    order_history 
              GROUP BY  
                      order_id 
              there... since [YEAR] and the others are numbers you cant use numbers as column names unless you use ' =D.

              Regards

              Comment


              • #8
                i got another error Parse error: syntax error, unexpected '$this' (T_VARIABLE)

                Comment


                • #9
                  ok im kinda confused now... where are you using that sql statemen

                  Comment


                  • #10
                    in SQL Select Statement when creating grid

                    Comment


                    • #11
                      share with me both screens please, the one with the error and the one with your query.

                      Regards

                      Comment


                      • #12
                        as i told you i had to delete the grid and create new one and now i got this error

                        Untitled1.pngUntitled2.png

                        Comment


                        • #13
                          what is the db type you are using?

                          kafe, this is not scriptcase issues, it is basic alphabets of programming, he is mixing lettuce with carrots

                          Comment


                          • #14
                            Leon
                            db MYSQL
                            can you please separate the lettuce out of carrots and show me the right code

                            Comment


                            • #15
                              hey

                              yes, i have many similar programs already built and I sent you private messages to share them if i knew what you want exactly as I have a long experience in these patients and treatment medical stuff but you never answered me

                              instead, you keep asking questions that are not related to scriptcase, but related to SQL statments and basics of programming


                              cheers

                              Comment

                              Working...
                              X