Hello, I have a column "Status" that lists the status of each individual as in the example below.

Status
--------
Married
Single
Divorced


I would like to get a count of how many records for each status in the format below. Is there a way to do this?



Result

Married   Single   Divorced
-------   ------   --------
10   12   5


Thanks,
Brent

Brent wrote:
> Hello, I have a column "Status" that lists the status of each
> individual as in the example below.
>
> Status
> --------
> Married
> Single
> Divorced
>
>
> I would like to get a count of how many records for each status in
> the format below. Is there a way to do this?

Do you know the different stati when you write the SQL statement? Then you
could do something like

SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...

What you can't build via SQL is a crosstable.

Ralf

"Ralf Mimoun" <nospam@rad-on.de> wrote:

Brent wrote:
> Hello, I have a column "Status" that lists the status of each
> individual as in the example below.
>
> Status
> --------
> Married
> Single
> Divorced
>
>
> I would like to get a count of how many records for each status in
> the format below. Is there a way to do this?

Do you know the different stati when you write the SQL statement? Then you
could do something like

SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...

What you can't build via SQL is a crosstable.

Ralf

"Ralf Mimoun" <nospam@rad-on.de> wrote:

Brent wrote:
> Hello, I have a column "Status" that lists the status of each
> individual as in the example below.
>
> Status
> --------
> Married
> Single
> Divorced
>
>
> I would like to get a count of how many records for each status in
> the format below. Is there a way to do this?

Do you know the different stati when you write the SQL statement? Then you
could do something like

SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...

What you can't build via SQL is a crosstable.

Ralf


Thanks Ralf,
That's what I was after. I did not realize that you could not build a cross table in SQL.

Try this:

SELECT
SUM(IF(STATUS='Married',1,0)) AS Married,
SUM(IF(STATUS='Single',1,0)) AS Single,
SUM(IF(STATUS='Divorced',1,0)) AS Divorced
FROM YOURTABLENAME

Instant CrossTab!

Chuck Hall

P.S. I don't know why my name isn't showing up in the From column. It is in
my properties for this newsgroup!  Another user said it's because my name
isn't in the header, but I don't know to get my name into the header.  I'm
using Outlook Express 6


"Brent" <forbes23669@yahoo.com> wrote in message
news:C26501BD-1A20-44A2-87D7-751196514129@news.elevatesoft.com...
> "Ralf Mimoun" <nospam@rad-on.de> wrote:
>
> Brent wrote:
>> Hello, I have a column "Status" that lists the status of each
>> individual as in the example below.
>>
>> Status
>> --------
>> Married
>> Single
>> Divorced
>>
>>
>> I would like to get a count of how many records for each status in
>> the format below. Is there a way to do this?
>
> Do you know the different stati when you write the SQL statement? Then you
> could do something like
>
> SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...
>
> What you can't build via SQL is a crosstable.
>
> Ralf
>
>
> Thanks Ralf,
> That's what I was after. I did not realize that you could not build a
> cross table in SQL.
>

Chuck

I don't know why your name isn't in the header its just not. Out of interest send me an email and lets see if its in there.

Roy Lambert

Thanks Chuck,
That worked nicely. I'm also trying apply this to Advantage DB Server since I use both DB engines. But its having problems with this code.

For now its great. Thanks again,

Brent

<> wrote:

Try this:

SELECT
SUM(IF(STATUS='Married',1,0)) AS Married,
SUM(IF(STATUS='Single',1,0)) AS Single,
SUM(IF(STATUS='Divorced',1,0)) AS Divorced
FROM YOURTABLENAME

Instant CrossTab!

Chuck Hall

P.S. I don't know why my name isn't showing up in the From column. It is in
my properties for this newsgroup!  Another user said it's because my name
isn't in the header, but I don't know to get my name into the header.  I'm
using Outlook Express 6


"Brent" <forbes23669@yahoo.com> wrote in message
news:C26501BD-1A20-44A2-87D7-751196514129@news.elevatesoft.com...
> "Ralf Mimoun" <nospam@rad-on.de> wrote:
>
> Brent wrote:
>> Hello, I have a column "Status" that lists the status of each
>> individual as in the example below.
>>
>> Status
>> --------
>> Married
>> Single
>> Divorced
>>
>>
>> I would like to get a count of how many records for each status in
>> the format below. Is there a way to do this?
>
> Do you know the different stati when you write the SQL statement? Then you
> could do something like
>
> SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...
>
> What you can't build via SQL is a crosstable.
>
> Ralf
>
>
> Thanks Ralf,
> That's what I was after. I did not realize that you could not build a
> cross table in SQL.
>

Thanks Chuck,
That worked nicely. I'm also trying apply this to Advantage DB Server since I use both DB engines. But its having problems with this code.

For now its great. Thanks again,

Brent

<> wrote:

Try this:

SELECT
SUM(IF(STATUS='Married',1,0)) AS Married,
SUM(IF(STATUS='Single',1,0)) AS Single,
SUM(IF(STATUS='Divorced',1,0)) AS Divorced
FROM YOURTABLENAME

Instant CrossTab!

Chuck Hall

P.S. I don't know why my name isn't showing up in the From column. It is in
my properties for this newsgroup!  Another user said it's because my name
isn't in the header, but I don't know to get my name into the header.  I'm
using Outlook Express 6


"Brent" <forbes23669@yahoo.com> wrote in message
news:C26501BD-1A20-44A2-87D7-751196514129@news.elevatesoft.com...
> "Ralf Mimoun" <nospam@rad-on.de> wrote:
>
> Brent wrote:
>> Hello, I have a column "Status" that lists the status of each
>> individual as in the example below.
>>
>> Status
>> --------
>> Married
>> Single
>> Divorced
>>
>>
>> I would like to get a count of how many records for each status in
>> the format below. Is there a way to do this?
>
> Do you know the different stati when you write the SQL statement? Then you
> could do something like
>
> SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...
>
> What you can't build via SQL is a crosstable.
>
> Ralf
>
>
> Thanks Ralf,
> That's what I was after. I did not realize that you could not build a
> cross table in SQL.
>

Brent:

That wouln't have worked in Oracle either.  In Advantage try a nested query
something like

SELECT SUM(M) AS Married,
SELECT SUM(S) AS Single,
SELECT SUM(D) AS Divorced FROM
(SELECT
IF(STATUS='Married',1,0) AS M,
IF(STATUS='Single',1,0) AS S,
IF(STATUS='Divorced',1,0) AS D
FROM YOURTABLENAME
)

I tried DBISAM this way first, but it couln't handle the nesting.  I was a
little surprised it got the right answers on my data without nesting.

Chuck Hall

"Brent" <forbes23669@yahoo.com> wrote in message
news:8D5BBCDB-9D17-44D1-B3E6-09072A707C41@news.elevatesoft.com...
> Thanks Chuck,
> That worked nicely. I'm also trying apply this to Advantage DB Server
> since I use both DB engines. But its having problems with this code.
>
> For now its great. Thanks again,
>
> Brent
>
> <> wrote:
>
> Try this:
>
> SELECT
> SUM(IF(STATUS='Married',1,0)) AS Married,
> SUM(IF(STATUS='Single',1,0)) AS Single,
> SUM(IF(STATUS='Divorced',1,0)) AS Divorced
> FROM YOURTABLENAME
>
> Instant CrossTab!
>
> Chuck Hall
>
> P.S. I don't know why my name isn't showing up in the From column. It is
> in
> my properties for this newsgroup!  Another user said it's because my name
> isn't in the header, but I don't know to get my name into the header.  I'm
> using Outlook Express 6
>
>
> "Brent" <forbes23669@yahoo.com> wrote in message
> news:C26501BD-1A20-44A2-87D7-751196514129@news.elevatesoft.com...
>> "Ralf Mimoun" <nospam@rad-on.de> wrote:
>>
>> Brent wrote:
>>> Hello, I have a column "Status" that lists the status of each
>>> individual as in the example below.
>>>
>>> Status
>>> --------
>>> Married
>>> Single
>>> Divorced
>>>
>>>
>>> I would like to get a count of how many records for each status in
>>> the format below. Is there a way to do this?
>>
>> Do you know the different stati when you write the SQL statement? Then
>> you
>> could do something like
>>
>> SELECT SUM(IF(Status='Married', 1, 0)) AS SumMarried,...
>>
>> What you can't build via SQL is a crosstable.
>>
>> Ralf
>>
>>
>> Thanks Ralf,
>> That's what I was after. I did not realize that you could not build a
>> cross table in SQL.
>>
>
>