Count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 30 Jan 2008 15:49 GMT

I am trying to count entries into each column heading by zip code, but when I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?

Thanks in advance.

SELECT    DISTINCT P.ZIP,
    COUNT (evC.day_care_center) AS 'Day Care',
    COUNT (evC.drop_care) AS 'Drop-In Care',
    COUNT (evC.family_day_care) AS 'Family day care',
    COUNT (evC.financial) AS 'Financial',
    COUNT (evC.montessori_program2)AS 'Montessori program',
    COUNT (evC.nanny_service) AS 'Nanny service',
    COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
    COUNT (evC.parenting_info) AS 'Parenting information',
    COUNT (evC.sick_child_care) AS 'Sick child care',
    COUNT (evC.special_needs)AS 'Special needs',
    COUNT (evC.summer_camp_care)AS 'Summer camps/care',
    COUNT (evC.temporary) AS 'Temporary',
    COUNT (evC.transportation2)AS 'Transportation',
    COUNT (evC.support_groups) AS 'Support groups',
    COUNT (evC.other) AS 'Other'

FROM Patient_Elg pe
    INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
    INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
    AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip

Reply to this Message

Tom Moreau - 30 Jan 2008 15:55 GMT

Put the DISTINCT keyword inside the COUNT():

SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
...

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I am trying to count entries into each column heading by zip code, but when
I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?

Thanks in advance.

SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'

FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip

Reply to this Message

Roy Harvey (SQL Server MVP) - 30 Jan 2008 16:07 GMT

I'm guessing you might need to COUNT the distinct values for each
column. If that is the case:

SELECT    P.ZIP,
    COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
    COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
etc.

If that is not what you need please elaborate on what you actually
hope to see.

Note that since you were already doing a GROUP BY there was no purpose
to having a DISTINCT for the entire query.

Roy Harvey
Beacon Falls, CT

>I am trying to count entries into each column heading by zip code, but when I
>run the script below I get the same count across all columns. Can someone
[quoted text clipped - 25 lines]
> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>GROUP BY P.Zip

Reply to this Message

jpettigrew - 30 Jan 2008 16:56 GMT

I made the changes you suggested and it did make the counts unique but they
are not correct by column heading/category. To check I displayed all the
information for zip code 55901 under the column 'Day Care' for '07. I came up
with a count of 200, but for this script I only get 11. How can I get all
records to count?

Thanks again for your help in advance

>I'm guessing you might need to COUNT the distinct values for each
>column. If that is the case:
[quoted text clipped - 18 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip

Reply to this Message

Tom Cooper - 30 Jan 2008 17:22 GMT

Usually the fastest way to get answers to questions like this is to give us
the SQL statements to create sample tables and load those tables with sample
data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
you would want to see from that sample data. That keeps us from having to
guess what you want, and you will get an answer that has been tested against
your sample data.

Tom

>I made the changes you suggested and it did make the counts unique but they
> are not correct by column heading/category. To check I displayed all the
[quoted text clipped - 28 lines]
>>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>>GROUP BY P.Zip

Reply to this Message

Jay - 30 Jan 2008 20:11 GMT

Because of HIPAA compliance it is difficult to provide information contained
in one of the tables. How do you suggest I provide the information? If i was
not specific in my previous posts I apologize I am trying to total the
columns in the script by zip code, but do not seem to be getting all the
information.

>Usually the fastest way to get answers to questions like this is to give us
>the SQL statements to create sample tables and load those tables with sample
[quoted text clipped - 10 lines]
>>>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>>>GROUP BY P.Zip

Reply to this Message

Hugo Kornelis - 30 Jan 2008 22:20 GMT

>Because of HIPAA compliance it is difficult to provide information contained
>in one of the tables. How do you suggest I provide the information?

Hi Jay,

* Post the table structure as CREATE TABLE statements, including all
constraints, properties, and indexes.

* Post the data as INSERT statements. It doesn't need to be the real
data, a made-up sample that illustrates the problem is just as well
(probably even better, as there is no need to posts thousands or even
millions of rows when you can illustrate the problem with ten). I don't
think HIPAA disallows the posting of made-up data.

* Post the expected results.

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

Roy Harvey (SQL Server MVP) - 30 Jan 2008 20:10 GMT

What Tom Cooper said. SHOW us what you have, and what you want.

Your original query counted all the rows for the zip code. All the
columns were the same because COUNT(columnname) returns a count of all
the rows with non-null values for that column, and apparently every
row was non-null. If there are 200 rows for a zip code, why would you
expect any number except 200 for day_care_center or drop_care?

Roy Harvey
Beacon Falls, CT

>I made the changes you suggested and it did make the counts unique but they
>are not correct by column heading/category. To check I displayed all the
[quoted text clipped - 26 lines]
>>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>>GROUP BY P.Zip

Reply to this Message

SB - 31 Jan 2008 04:40 GMT

> I am trying to count entries into each column heading by zip code, but when I
> run the script below I get the same count across all columns. Can someone
[quoted text clipped - 28 lines]
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1

Hi Jay,
I think you need to do each count(distinct field) separately and store
them (maybe into a variable). For example:

Declare @zip int, @center int, etc
Select @zip = select count(distinct p.zip)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =
evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip

Select @center int = select count(distinct evC.day_care_center)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =
evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip

etc.

HTH

Reply to this Message

Roy Harvey (SQL Server MVP) - 31 Jan 2008 13:00 GMT

>I think you need to do each count(distinct field) separately and store
>them (maybe into a variable).

All at once or individually, if they are always grouped by the same
thing there will be no difference.

Roy Harvey
Beacon Falls, CT

Reply to this Message