Loading ...
Sorry, an error occurred while loading the content.
 

QUERY TO GET THE FIRST 5 RECORDS WITHE THE HIGHEST VOLUME

Expand Messages
  • robert sackey
    Hi, Need little help. I have about 1 million rows in a table. The table has memberID field , volume field and Fee Field. I want a query that will give me the
    Message 1 of 13 , Feb 1, 2008
      Hi,

      Need little help.

      I have about 1 million rows in a table. The table has memberID field , volume field and Fee Field.

      I want a query that will give me the first five memberID with the highest total volume. In other words, for each member ID , i neeed the sum of all volumes used by the MemberID and sum of Fees for the memberID and I need only the first 5 memberID with the highest volume. Very urgent pls.

      Thanks in advance for the assistance.


      ____________________________________________________________________________________
      Be a better friend, newshound, and
      know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


      [Non-text portions of this message have been removed]
    • Nature Strikes Back
      first u must create the column of volume in descending order and then write the query ... From: robert sackey To: SQLQueriesNoCode@yahoogroups.com Sent:
      Message 2 of 13 , Feb 1, 2008
        first u must create the column of volume in descending order and then write the query
        ----- Original Message -----
        From: robert sackey
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Friday, February 01, 2008 1:45 PM
        Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE HIGHEST VOLUME


        Hi,

        Need little help.

        I have about 1 million rows in a table. The table has memberID field , volume field and Fee Field.

        I want a query that will give me the first five memberID with the highest total volume. In other words, for each member ID , i neeed the sum of all volumes used by the MemberID and sum of Fees for the memberID and I need only the first 5 memberID with the highest volume. Very urgent pls.

        Thanks in advance for the assistance.

        __________________________________________________________
        Be a better friend, newshound, and
        know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

        [Non-text portions of this message have been removed]





        [Non-text portions of this message have been removed]
      • Paul Livengood
        Robert; There are two common ways to do this. 1 SELECT TOP 5 MemberID, sum(Volume) as SumVolume, sum(Fee) as SumFee From Table Group by memberid, Order by
        Message 3 of 13 , Feb 1, 2008
          Robert;



          There are two common ways to do this.





          1

          SELECT TOP 5 MemberID, sum(Volume) as SumVolume, sum(Fee) as
          SumFee

          From Table

          Group by memberid,

          Order by sum(Volume) desc



          2

          Set Rowcount 5

          SELECT MemberID, sum(Volume) as SumVolume, sum(Fee) as
          SumFee

          From Table

          Group by MemberID

          Order by sum(Volume) desc



          Be aware that it could take a while to run this query because it will have
          to calculate the entire table before it can decide which items have the
          highest volume.

          The advantage to the second way is that you can set the rowcount with a
          variable, but the TOP keyword can only use whole numbers or percents (Top 5
          percent).



          Hope that helps



          Paul



          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of robert sackey
          Sent: Friday, February 01, 2008 1:46 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE
          HIGHEST VOLUME



          Hi,

          Need little help.

          I have about 1 million rows in a table. The table has memberID field ,
          volume field and Fee Field.

          I want a query that will give me the first five memberID with the highest
          total volume. In other words, for each member ID , i neeed the sum of all
          volumes used by the MemberID and sum of Fees for the memberID and I need
          only the first 5 memberID with the highest volume. Very urgent pls.

          Thanks in advance for the assistance.

          __________________________________________________________
          Be a better friend, newshound, and
          know-it-all with Yahoo! Mobile. Try it now.
          http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

          [Non-text portions of this message have been removed]





          [Non-text portions of this message have been removed]
        • Anca, Paul
          Here you are: SELECT TOP 5 memberID, SUM(volume), SUM(Fee) FROM table GROUP BY memberID ORDER BY SUM(volume) DESC Hope it helps, Paul Anca
          Message 4 of 13 , Feb 1, 2008
            Here you are:

            SELECT TOP 5
            memberID, SUM(volume), SUM(Fee)
            FROM
            table
            GROUP BY
            memberID
            ORDER BY
            SUM(volume) DESC

            Hope it helps,

            Paul Anca

            paul@...
            www.ancasolutions.com



            ----- Original Message -----
            From: "robert sackey" <papsac@...>
            To: <SQLQueriesNoCode@yahoogroups.com>
            Sent: Friday, February 01, 2008 3:45 AM
            Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE
            HIGHEST VOLUME


            > Hi,
            >
            > Need little help.
            >
            > I have about 1 million rows in a table. The table has memberID field ,
            > volume field and Fee Field.
            >
            > I want a query that will give me the first five memberID with the highest
            > total volume. In other words, for each member ID , i neeed the sum of all
            > volumes used by the MemberID and sum of Fees for the memberID and I need
            > only the first 5 memberID with the highest volume. Very urgent pls.
            >
            > Thanks in advance for the assistance.
            >
            >
            >
            > ____________________________________________________________________________________
            > Be a better friend, newshound, and
            > know-it-all with Yahoo! Mobile. Try it now.
            > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
            >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
          • John Warner
            SELECT TOP 5 T.memberID, SUM(T.volume), SUM(T.fee), FROM TheTable T GROUP BY T.memberID ORDER BY T.volume Without data to play with, this is a guess but I
            Message 5 of 13 , Feb 1, 2008
              SELECT TOP 5
              T.memberID,
              SUM(T.volume),
              SUM(T.fee),

              FROM
              TheTable T

              GROUP BY
              T.memberID

              ORDER BY
              T.volume

              Without data to play with, this is a guess but I think it will fetch what
              you want. I'm not near (network wise) a database right now to test this
              for myself. Also this is T-SQL, you don't specify the DBMS so I made the
              choice for you.

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of robert sackey
              > Sent: Friday, February 01, 2008 3:46 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS
              > WITHE THE HIGHEST VOLUME
              >
              > Hi,
              >
              > Need little help.
              >
              > I have about 1 million rows in a table. The table has memberID field ,
              volume
              > field and Fee Field.
              >
              > I want a query that will give me the first five memberID with the
              highest total
              > volume. In other words, for each member ID , i neeed the sum of all
              volumes
              > used by the MemberID and sum of Fees for the memberID and I need only
              the
              > first 5 memberID with the highest volume. Very urgent pls.
              >
              > Thanks in advance for the assistance.
              >
              >
              >
              > _______________________________________________________________
              > _____________________
              > Be a better friend, newshound, and
              > know-it-all with Yahoo! Mobile. Try it now.
              > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
              >
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Carmen Popescu
              I don t know how this works in other DBMS but in Oracle SQL I can explain you a little... select * from (select memberId, sum(volum) as sumVol, sum(fee) as
              Message 6 of 13 , Feb 1, 2008
                I don't know how this works in other DBMS but in Oracle SQL I can explain you a little...
                select *
                from (select memberId, sum(volum) as sumVol,
                sum(fee) as sumFee
                from table
                group by memberId
                order by sumVol)
                where rownum<=5

                You need subquery because rownum select the rows before ordering them, so you'll receive the first 5 rows in the unordered table, and only after that this rows are ordered.

                I'm not sure if I write correctly, but in MySQL maybe the anser is like:
                select top 5 * from
                (select memberId, sum(fee), sum(volum)
                from table
                group by memberId
                order by sum(fee))



                ---------------------------------------
                Carmen Popescu
                "Gheorghe Lazar" National College
                Sibiu - Romania
                Oracle Academy Lead Adjunct
                http://www.pc-info.ro

                ---------------------------------
                Never miss a thing. Make Yahoo your homepage.

                [Non-text portions of this message have been removed]
              • Nature Strikes Back
                dear it is not valid ur query ... From: Anca, Paul To: SQLQueriesNoCode@yahoogroups.com Sent: Friday, February 01, 2008 5:47 PM Subject: Re: [SQLQueriesNoCode]
                Message 7 of 13 , Feb 2, 2008
                  dear it is not valid ur query
                  ----- Original Message -----
                  From: Anca, Paul
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Friday, February 01, 2008 5:47 PM
                  Subject: Re: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE HIGHEST VOLUME


                  Here you are:

                  SELECT TOP 5
                  memberID, SUM(volume), SUM(Fee)
                  FROM
                  table
                  GROUP BY
                  memberID
                  ORDER BY
                  SUM(volume) DESC

                  Hope it helps,

                  Paul Anca

                  paul@...
                  www.ancasolutions.com

                  ----- Original Message -----
                  From: "robert sackey" <papsac@...>
                  To: <SQLQueriesNoCode@yahoogroups.com>
                  Sent: Friday, February 01, 2008 3:45 AM
                  Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE
                  HIGHEST VOLUME

                  > Hi,
                  >
                  > Need little help.
                  >
                  > I have about 1 million rows in a table. The table has memberID field ,
                  > volume field and Fee Field.
                  >
                  > I want a query that will give me the first five memberID with the highest
                  > total volume. In other words, for each member ID , i neeed the sum of all
                  > volumes used by the MemberID and sum of Fees for the memberID and I need
                  > only the first 5 memberID with the highest volume. Very urgent pls.
                  >
                  > Thanks in advance for the assistance.
                  >
                  >
                  >
                  > __________________________________________________________
                  > Be a better friend, newshound, and
                  > know-it-all with Yahoo! Mobile. Try it now.
                  > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >





                  [Non-text portions of this message have been removed]
                • Anca, Paul
                  why? ... From: Nature Strikes Back To: Sent: Saturday, February 02, 2008 4:16 AM
                  Message 8 of 13 , Feb 2, 2008
                    why?

                    ----- Original Message -----
                    From: "Nature Strikes Back" <naturestrikesback@...>
                    To: <SQLQueriesNoCode@yahoogroups.com>
                    Sent: Saturday, February 02, 2008 4:16 AM
                    Subject: Re: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE
                    HIGHEST VOLUME


                    > dear it is not valid ur query
                    > ----- Original Message -----
                    > From: Anca, Paul
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Sent: Friday, February 01, 2008 5:47 PM
                    > Subject: Re: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE
                    > THE HIGHEST VOLUME
                    >
                    >
                    > Here you are:
                    >
                    > SELECT TOP 5
                    > memberID, SUM(volume), SUM(Fee)
                    > FROM
                    > table
                    > GROUP BY
                    > memberID
                    > ORDER BY
                    > SUM(volume) DESC
                    >
                    > Hope it helps,
                    >
                    > Paul Anca
                    >
                    > paul@...
                    > www.ancasolutions.com
                    >
                    > ----- Original Message -----
                    > From: "robert sackey" <papsac@...>
                    > To: <SQLQueriesNoCode@yahoogroups.com>
                    > Sent: Friday, February 01, 2008 3:45 AM
                    > Subject: [SQLQueriesNoCode] QUERY TO GET THE FIRST 5 RECORDS WITHE THE
                    > HIGHEST VOLUME
                    >
                    > > Hi,
                    > >
                    > > Need little help.
                    > >
                    > > I have about 1 million rows in a table. The table has memberID field ,
                    > > volume field and Fee Field.
                    > >
                    > > I want a query that will give me the first five memberID with the
                    > highest
                    > > total volume. In other words, for each member ID , i neeed the sum of
                    > all
                    > > volumes used by the MemberID and sum of Fees for the memberID and I
                    > need
                    > > only the first 5 memberID with the highest volume. Very urgent pls.
                    > >
                    > > Thanks in advance for the assistance.
                    > >
                    > >
                    > >
                    > > __________________________________________________________
                    > > Be a better friend, newshound, and
                    > > know-it-all with Yahoo! Mobile. Try it now.
                    > > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
                    > >
                    > >
                    > > [Non-text portions of this message have been removed]
                    > >
                    > >
                    > >
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    > >
                    >
                    >
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                  • O S
                    I am trying use if statement but its not working, i want to times price by 1 when discount/100 is 0 (discount could be null hence giving 0) , Price * ( IF
                    Message 9 of 13 , Feb 4, 2008
                      I am trying use if statement but its not working, i want to times price by 1 when discount/100 is 0 (discount could be null hence giving 0)

                      , Price * (
                      IF (ShippingDiscountPercent/100 = 0)
                      1
                      ELSE
                      ShippingDiscountPercent/100
                      ) as PriceCost






                      _________________________________________________________________
                      Get Hotmail on your mobile, text MSN to 63463!
                      http://mobile.uk.msn.com/pc/mail.aspx

                      [Non-text portions of this message have been removed]
                    • Paul Anca
                      For this king of operations you need to use CASE not IF statements, something like this: SELECT PriceCost = CASE (ShippingDiscountPercent/100 = 0) WHEN 0 THEN
                      Message 10 of 13 , Feb 4, 2008
                        For this king of operations you need to use CASE not IF statements, something like this:

                        SELECT PriceCost =
                        CASE (ShippingDiscountPercent/100 = 0)
                        WHEN 0 THEN Price
                        ELSE Price * ShippingDiscountPercent/100
                        END,

                        Hope it helps,
                        Paul Anca

                        www.ancasolutions.com


                        >
                        >
                        >
                        >I am trying use if statement but its not working, i want to times price by 1
                        >when discount/100 is 0 (discount could be null hence giving 0)
                        >
                        >, Price * (
                        >IF (ShippingDiscountPercent/100 = 0)
                        >1
                        >ELSE
                        >ShippingDiscountPercent/100
                        >) as PriceCost
                        >
                        >
                        >
                        >
                        >
                        >
                        >_________________________________________________________________
                        >Get Hotmail on your mobile, text MSN to 63463!
                        >http://mobile.uk.msn.com/pc/mail.aspx
                        >
                        >[Non-text portions of this message have been removed]
                        >
                        >
                        >
                        >
                        >Yahoo! Groups Links
                        >
                        >
                        >
                      • Abhilash S Nair
                        use *begin *end *block* IF (ShippingDiscountPercent/100 = 0) *begin* 1 *end* ELSE *begin* ShippingDiscountPercent/100 *end* ) as PriceCost use *begin *end
                        Message 11 of 13 , Feb 4, 2008
                          use *begin *end *block*

                          IF (ShippingDiscountPercent/100 = 0)
                          *begin*
                          1
                          *end*
                          ELSE
                          *begin*
                          ShippingDiscountPercent/100
                          *end*
                          ) as PriceCost


                          use *begin *end *block*


                          [Non-text portions of this message have been removed]
                        • Paul Anca
                          Hi, Usage of *begin *end *block* is necessary only when you have more than 1 command (a block of commands) on IF or ELSE branch: IF (condition) BEGIN COMMAND1
                          Message 12 of 13 , Feb 4, 2008
                            Hi,

                            Usage of *begin *end *block* is necessary only when you have more than 1 command (a block of commands) on IF or ELSE branch:


                            IF (condition)
                            BEGIN
                            COMMAND1
                            COMMAND2
                            ..........
                            END
                            ELSE
                            BEGIN
                            COMMAND3
                            COMMAND4
                            ..........
                            END

                            In the original example, each branch of IF command have only one command to execute, it this case begin/end keywords are not necessary, problem's not here, not in IF syntax.

                            The issue on original question is that you can't use IF statement in SELECT, you need to use CASE as I show previously:

                            SELECT
                            PriceCost =

                            CASE (ShippingDiscountPercent/100)

                            WHEN 0 THEN Price

                            ELSE Price * ShippingDiscountPercent/100

                            END

                            FROM ....


                            Hope it helps,
                            Paul Anca

                            www.ancasolutions.com



                            > use *begin *end *block*
                            >
                            >IF (ShippingDiscountPercent/100 = 0)
                            >*begin*
                            >1
                            >*end*
                            >ELSE
                            >*begin*
                            >ShippingDiscountPercent/100
                            >*end*
                            >) as PriceCost
                            >
                            >
                            >use *begin *end *block*
                            >
                            >
                            >[Non-text portions of this message have been removed]
                            >
                            >
                            >
                            >
                            >Yahoo! Groups Links
                            >
                            >
                            >
                          • Max Parmenter
                            You could also use DECODE in Oracle. cheers Max ... From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of O S Sent: 04
                            Message 13 of 13 , Feb 4, 2008
                              You could also use DECODE in Oracle.

                              cheers

                              Max

                              -----Original Message-----
                              From: SQLQueriesNoCode@yahoogroups.com
                              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of O S
                              Sent: 04 February 2008 12:42
                              To: sqlqueriesnocode@yahoogroups.com
                              Subject: [SQLQueriesNoCode] IF ELSE in SQL





                              I am trying use if statement but its not working, i want to times price by 1
                              when discount/100 is 0 (discount could be null hence giving 0)

                              , Price * (
                              IF (ShippingDiscountPercent/100 = 0)
                              1
                              ELSE
                              ShippingDiscountPercent/100
                              ) as PriceCost

                              __________________________________________________________
                              Get Hotmail on your mobile, text MSN to 63463!
                              http://mobile. <http://mobile.uk.msn.com/pc/mail.aspx>
                              uk.msn.com/pc/mail.aspx

                              [Non-text portions of this message have been removed]







                              [Non-text portions of this message have been removed]
                            Your message has been successfully submitted and would be delivered to recipients shortly.