• ## RE: [SQLQueriesNoCode] Query Problem

(21)
• NextPrevious
• Thank you, without the data I wasn t sure why mine was wrong. John Warner
Message 1 of 21 , Feb 1, 2005
View Source
• 0 Attachment
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)
> > >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
• 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
View Source
• 0 Attachment
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.
• 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
View Source
• 0 Attachment
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.
>
>
>
>
>
>
>
>
>
>
>
• 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
View Source
• 0 Attachment
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

• 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
View Source
• 0 Attachment
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
• 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
View Source
• 0 Attachment
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]
• 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
View Source
• 0 Attachment
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

[Non-text portions of this message have been removed]
• 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
View Source
• 0 Attachment
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

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.