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

RE: [SQLQueriesNoCode] Query Problem

Expand Messages
  • John Warner
    Thank you, without the data I wasn t sure why mine was wrong. John Warner
    Message 1 of 21 , Feb 1, 2005
      Thank you, without the data I wasn't sure why mine was wrong.

      John Warner




      > -----Original Message-----
      > From: Michael Gerholdt [mailto:Michael.Gerholdt@...]
      > Sent: Monday, January 31, 2005 2:02 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] Query Problem
      >
      >
      >
      >
      > John,
      >
      > Your query returns all family members in all families. All
      > the max aggregate is doing here is getting the max age for
      > each person in each family; so that if a given family member
      > were listed twice in the same family with two different ages,
      > only the older age record would get pulled.
      >
      > My query (and there may be better ways of doing it) may pull
      > more than one family member per family, but only if they are
      > all the same age as the oldest in their family.
      >
      > Mike
      >
      >
      >
      > John's suggestion:
      > >
      > > To show my ignorance (and learn from it), why wouldn't this work?
      > >
      > > SELECT MAX(age), name, [family number]
      > > FROM Table
      > > GROUP BY [family number], name;
      > >
      >
      > Mike's suggestion:
      > > >
      > > > So you can do this:
      > > >
      > > > Select a.name, a.age
      > > > From myTable a
      > > > Where a.age = (select max(b.age)
      > > > from myTable b
      > > > where b.FamilyNumber = a.FamilyNumber)
      > > >
      >
      >
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
      >
      >
    • Damhuis Anton
      Hi John I am glad to see you had a stab at a possible solution. Some people would not attempt that, for fear of being flamed. Good going. Regards Anton ...
      Message 2 of 21 , Feb 1, 2005
        Hi John

        I am glad to see you had a stab at a possible solution.
        Some people would not attempt that, for fear of being flamed.

        Good going.

        Regards
        Anton

        -----Original Message-----
        From: John Warner [mailto:john@...]
        Sent: 01 February 2005 11:30
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: RE: [SQLQueriesNoCode] Query Problem



        Thank you, without the data I wasn't sure why mine was wrong.

        John Warner

        > John's suggestion:
        > >
        > > To show my ignorance (and learn from it), why wouldn't this work?
        > >

        Confidentiality Warning
        =======================

        The contents of this e-mail and any accompanying documentation
        are confidential and any use thereof, in what ever form, by anyone
        other than the addressee is strictly prohibited.
      • John Warner
        Wasn t that, I knew my idea would not produce the correct result, I just didn t know why. John Warner
        Message 3 of 21 , Feb 1, 2005
          Wasn't that, I knew my idea would not produce the correct result, I just
          didn't know why.

          John Warner




          > -----Original Message-----
          > From: Damhuis Anton [mailto:DamhuisA@...]
          > Sent: Tuesday, February 01, 2005 4:34 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Query Problem
          >
          >
          >
          >
          > Hi John
          >
          > I am glad to see you had a stab at a possible solution.
          > Some people would not attempt that, for fear of being flamed.
          >
          > Good going.
          >
          > Regards
          > Anton
          >
          > -----Original Message-----
          > From: John Warner [mailto:john@...]
          > Sent: 01 February 2005 11:30
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Query Problem
          >
          >
          >
          > Thank you, without the data I wasn't sure why mine was wrong.
          >
          > John Warner
          >
          > > John's suggestion:
          > > >
          > > > To show my ignorance (and learn from it), why wouldn't this work?
          > > >
          >
          > Confidentiality Warning
          > =======================
          >
          > The contents of this e-mail and any accompanying
          > documentation are confidential and any use thereof, in what
          > ever form, by anyone other than the addressee is strictly prohibited.
          >
          >
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
          >
          >
        • syravirgo
          Hello all i have a status field in table attendance in SQL Server status ... Present Present Absent Absent Present Leave Leave Present I want to count number
          Message 4 of 21 , Jun 20, 2006
            Hello all

            i have a status field in table attendance in SQL Server

            status
            -------
            Present
            Present
            Absent
            Absent
            Present
            Leave
            Leave
            Present

            I want to count number of presence absence and leaves of each
            employee, do any 1 knows a single query for this ?

            I need result in this format below

            Present Absent Leave
            ------- ------- -------
            4 2 2


            waiting for an earliest reply
          • John Warner
            Need more info, how do I know the first Present is for you and the second present is for Jane? Or do you just want total Present, total Absent etc? John Warner
            Message 5 of 21 , Jun 20, 2006
              Need more info, how do I know the first Present is for you and the
              second present is for Jane? Or do you just want total Present, total
              Absent etc?

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of syravirgo
              > Sent: Tuesday, June 20, 2006 7:46 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] Query Problem
              >
              >
              > Hello all
              >
              > i have a status field in table attendance in SQL Server
              >
              > status
              > -------
              > Present
              > Present
              > Absent
              > Absent
              > Present
              > Leave
              > Leave
              > Present
              >
              > I want to count number of presence absence and leaves of each
              > employee, do any 1 knows a single query for this ?
              >
              > I need result in this format below
              >
              > Present Absent Leave
              > ------- ------- -------
              > 4 2 2
              >
              >
              > waiting for an earliest reply
            • my.yudis
              select xx.status as present, yy.status as absent, zz.status as leave from ( select a as dummy, count(status) as status from temp where status = Present )
              Message 6 of 21 , Jun 20, 2006
                select xx.status as present, yy.status as absent, zz.status as leave
                from
                ( select 'a' as dummy, count(status) as status
                from temp
                where status = 'Present'
                ) xx
                inner join
                ( select 'a' as dummy, count(status) as status
                from temp
                where status = 'Absent'
                ) yy ON xx.dummy = yy.dummy
                inner join
                ( select 'a' as dummy, count(status) as status
                from temp
                where status = 'Leave'
                ) zz ON yy.dummy = zz.dummy

                Best Regards,
                - Drury Yudistira Lumenta -

                On 6/20/06, syravirgo <syravirgo@...> wrote:
                >
                > Hello all
                >
                > i have a status field in table attendance in SQL Server
                >
                > status
                > -------
                > Present
                > Present
                > Absent
                > Absent
                > Present
                > Leave
                > Leave
                > Present
                >
                > I want to count number of presence absence and leaves of each
                > employee, do any 1 knows a single query for this ?
                >
                > I need result in this format below
                >
                > Present Absent Leave
                > ------- ------- -------
                > 4 2 2
                >
                > waiting for an earliest reply
                >
                >


                [Non-text portions of this message have been removed]
              • Mayowa Omosebi
                you can try this: select employee_name, status,count(status) from your _table group by employee_name,status --employee_name could be any field that uniquely
                Message 7 of 21 , Jun 20, 2006
                  you can try this:

                  select employee_name, status,count(status) from your _table
                  group by employee_name,status

                  --employee_name could be any field that uniquely identifies employees.

                  Regards

                  ----- Original Message ----
                  From: syravirgo <syravirgo@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Tuesday, June 20, 2006 12:46:23 PM
                  Subject: [SQLQueriesNoCode] Query Problem


                  Hello all

                  i have a status field in table attendance in SQL Server

                  status
                  -------
                  Present
                  Present
                  Absent
                  Absent
                  Present
                  Leave
                  Leave
                  Present

                  I want to count number of presence absence and leaves of each
                  employee, do any 1 knows a single query for this ?

                  I need result in this format below

                  Present Absent Leave
                  ------- ------- -------
                  4 2 2

                  waiting for an earliest reply




                  [Non-text portions of this message have been removed]
                • Arnie Rowland
                  You could try something like this: SELECT Employee --Name or ID , sum( CASE Status WHEN Present THEN 1 ELSE 0 END ) AS Present , sum( CASE Status WHEN
                  Message 8 of 21 , Jun 20, 2006
                    You could try something like this:

                    SELECT
                    Employee --Name or ID
                    , sum( CASE Status WHEN 'Present' THEN 1 ELSE 0 END ) AS 'Present'
                    , sum( CASE Status WHEN 'Absent' THEN 1 ELSE 0 END ) AS 'Absent'
                    , sum( CASE Status WHEN 'Leave' THEN 1 ELSE 0 END ) AS 'Leave'
                    FROM Attendance
                    GROUP BY Employee

                    Results will look like this:

                    Employee Present Absent Leave
                    ---------- ----------- ----------- -----------
                    Bill 2 1 0
                    Jane 2 1 2

                    For this data:

                    INSERT INTO ATTENDANCE VALUES ('Jane', 'Present')
                    INSERT INTO ATTENDANCE VALUES ('Jane', 'Present')
                    INSERT INTO ATTENDANCE VALUES ('Bill', 'Absent')
                    INSERT INTO ATTENDANCE VALUES ('Jane', 'Absent')
                    INSERT INTO ATTENDANCE VALUES ('Bill', 'Present')
                    INSERT INTO ATTENDANCE VALUES ('Jane', 'Leave')
                    INSERT INTO ATTENDANCE VALUES ('Jane', 'Leave')
                    INSERT INTO ATTENDANCE VALUES ('Bill', 'Present')

                    I hope that helps.

                    - Arnie Rowland

                    "I am a great believer in luck, and I find that the harder I work, the more
                    I have of it." - Thomas Jefferson (1743-1826)


                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com
                    [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of syravirgo
                    Sent: Tuesday, June 20, 2006 5:51 AM
                    To: Arnie
                    Subject: [SQLQueriesNoCode] Query Problem

                    Hello all

                    i have a status field in table attendance in SQL Server

                    status
                    -------
                    Present
                    Present
                    Absent
                    Absent
                    Present
                    Leave
                    Leave
                    Present

                    I want to count number of presence absence and leaves of each
                    employee, do any 1 knows a single query for this ?

                    I need result in this format below

                    Present Absent Leave
                    ------- ------- -------
                    4 2 2


                    waiting for an earliest reply
















                    Yahoo! Groups Links










                    Disclaimer - June 20, 2006
                    This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                    This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


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