i wondering if there way distinct count on column based on value of second column while still getting total count of first column. example of issue i'm facing. have query returns i-vent type, id, status, , linked medication orders pharmacy intervention system. interventions grouped i-vent type. status can 1 of 5 values or null. need able count how many i-vents recorded each of 6 possible values status.
an example set may similar this:
________________________________________________________ type | id | status | linked meds ________________________________________________________ iv2po | 1234 | accepted | pantoprazole iv iv2po | 1234 | accepted | pantoprazole po iv2po | 1235 | null | null iv2po | 1236 | pending | metoclopramide iv iv2po | 1236 | pending | metoclopramide po iv2po | 1236 | pending | pharmacy consult - iv2po consult | 1237 | rejected | null ________________________________________________________ the group summary should list iv2po having total count of 3 count of 1 "accepted", 1 "null", , 1 "pending"; , consult having total count of 1 count of 1 "rejected".
please take notice of duplicate values caused having more 1 medication/order liked i-vent.
ultimately i'm building final report in crystal reports if there way correct counts there fine well. have version of uses subreport linked medications/orders, i'd find better alternative take less time run , use fewer resources.
does know of way this?
thanks!
in crystal reports can use count distinct summary option
when creating "summary", using count function may not desirable. case report must return number of unique contact records, other tables (i.e. history) may contain multiple rows each customer. select insert | summary. select fieldname wish summarize. make sure select distinct count summary operation.
Comments
Post a Comment