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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Put 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',
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jay via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efd1fc265539@.uwe...
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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||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
On Wed, 30 Jan 2008 15:49:59 GMT, "Jay via SQLMonster.com" <u7124@.uwe>
wrote:
>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|||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
Roy Harvey (SQL Server MVP) wrote:
>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
--
Message posted via http://www.sqlmonster.com|||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
"jpettigrew via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efdb43406fee@.uwe...
>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
> Roy Harvey (SQL Server MVP) wrote:
>>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
> --
> Message posted via http://www.sqlmonster.com
>|||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
On Wed, 30 Jan 2008 16:56:33 GMT, "jpettigrew via SQLMonster.com"
<u7124@.uwe> wrote:
>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
>Roy Harvey (SQL Server MVP) wrote:
>>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|||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.
Tom Cooper wrote:
>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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||On Wed, 30 Jan 2008 20:11:22 GMT, Jay via SQLMonster.com wrote:
>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.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jan 30, 9:49=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> I am trying to count entries into each column heading by zip code, but whe=n 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 =A0DISTINCT P.ZIP,
> =A0 =A0 =A0 =A0 COUNT (evC.day_care_center) AS 'Day Care',
> =A0 =A0 =A0 =A0 COUNT (evC.drop_care) AS 'Drop-In Care',
> =A0 =A0 =A0 =A0 COUNT (evC.family_day_care) AS 'Family day care',
> =A0 =A0 =A0 =A0 COUNT (evC.financial) AS 'Financial',
> =A0 =A0 =A0 =A0 COUNT (evC.montessori_program2)AS 'Montessori program',
> =A0 =A0 =A0 =A0 COUNT (evC.nanny_service) AS 'Nanny service',
> =A0 =A0 =A0 =A0 COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn= centers',
> =A0 =A0 =A0 =A0 COUNT (evC.parenting_info) AS 'Parenting information',
> =A0 =A0 =A0 =A0 COUNT (evC.sick_child_care) AS 'Sick child care',
> =A0 =A0 =A0 =A0 COUNT (evC.special_needs)AS 'Special needs',
> =A0 =A0 =A0 =A0 COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> =A0 =A0 =A0 =A0 COUNT (evC.temporary) AS 'Temporary',
> =A0 =A0 =A0 =A0 COUNT (evC.transportation2)AS 'Transportation',
> =A0 =A0 =A0 =A0 COUNT (evC.support_groups) AS 'Support groups',
> =A0 =A0 =A0 =A0 COUNT (evC.other) AS 'Other'
> FROM =A0 =A0Patient_Elg pe
> =A0 =A0 =A0 =A0 INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
> =A0 =A0 =A0 =A0 INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D ev=C.Patient_Key
> WHERE =A0 =A0 (pe.Payor_Key =3D 59)
> =A0 =A0 =A0 =A0 AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
> GROUP BY P.Zip
> --
> 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 =3D select count(distinct p.zip)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
Select @.center int =3D select count(distinct evC.day_care_center)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
etc.
HTH|||On Wed, 30 Jan 2008 20:40:12 -0800 (PST), SB <othellomy@.yahoo.com>
wrote:
>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
No comments:
Post a Comment