SQL query yielding count and average

Question

I have two queries. One yields the total number of records for a specific procedure type. The other yields the number of records for that specific procedure by the species

I am trying to figure out how I can take and get both the number of counts and the average number for each of the specific procedure types.

Query yielding total number by procedure type:

/* The following query will show the total number of studies by imaging area. */
SELECT 
    PlacerFld2 AS "Type of Procedure", Count(*) AS "Count of Procedures"
FROM 
    [order] o
WHERE 
    lastmodifieddate BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 23:59:59'
    AND SiteBridgeID = '1' /* Medical Imaging */
    AND OrderStatusID <> '4'
    AND placerfld2 IN ('CARD', 'CARM', 'CRFL', 'CT', 'I131', 'LACR', 'LAUS', 'NUCMED','OUTFM','PCCR','PETCT','PETR','PHMR','SACR','SAES','SAMR','SAUS','VRC','VRUS')
GROUP BY 
    PlacerFld2
ORDER BY 
    PlacerFld2 ASC

Example output:

CT    2056
SAMR  1800
SACR  3600

Query yielding total number of procedures by species

/* The following query will break down the number of studies by species */
SELECT PlacerFld2 AS "Type of Procedure", City AS Species, Count(*) AS "Count of Procedures"
FROM [order] o

LEFT JOIN Visit v
ON o.VisitID = v.VisitID

LEFT JOIN PatientInfo pif
ON v.PatientID = pif.PatientID

LEFT JOIN Patient p
ON pif.PatientID = p.PatientID

LEFT JOIN PersonalInfo perinfo
ON p.PersonalInfoID = perinfo.PersonalInfoID

WHERE o.lastmodifieddate between '2017-01-01 00:00:00' AND '2017-12-31 23:59:59'
AND SiteBridgeID = '1' /* Medical Imaging */
AND OrderStatusID <> '4'
AND placerfld2 IN ('CARD','CARM','CRFL','CT','I131','LACR','LAUS','NUCMED','OUTFM','PCCR','PETCT','PETR','PHMR','SACR','SAES','SAMR','SAUS','VRC','VRUS')

GROUP BY Placerfld2, CITY
ORDER BY placerfld2 ASC, CITY ASC

Example Table Output:

CT    CANINE   1500
CT    FELINE   556
SAMR  CANINE   1000
SAMR  FELINE   600
SAMR  EQUINE   200

Desired Results:

CT    CANINE   1500   72.9%
CT    FELINE   556    27.1%
SAMR  CANINE   1000   55.5%
SAMR  FELINE   600    33.3%
SAMR  EQUINE   200    11.1%

Tag your question with the database you are using. – Gordon Linoff Aug 01 '18 at 14:35 — Gordon Linoff, Aug 01 '18 at 14:35

score 0 · Answer 1 · answered Aug 01 '18 at 14:36

0

You can use window functions:

with cte as (<your query here with no order by>)
select col1, col2, col3,
       col3 * 100.0 / sum(col3) over (partition by col1) as percentage
from cte;

answered Aug 01 '18 at 14:36

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Aug 01 '18 at 14:43

If common table expressions aren't an option for you (because they aren't supported, or you just don't like them), you could do it this way with a subquery:

/* The following query will break down the number of studies by species */
SELECT PlacerFld2 AS "Type of Procedure", City AS Species, Count(*) AS "Count of Procedures", COUNT(*) / x.[Count of Procedures] AS "Percentage"
FROM [order] o

LEFT JOIN Visit v
ON o.VisitID = v.VisitID

LEFT JOIN PatientInfo pif
ON v.PatientID = pif.PatientID

LEFT JOIN Patient p
ON pif.PatientID = p.PatientID

LEFT JOIN PersonalInfo perinfo
ON p.PersonalInfoID = perinfo.PersonalInfoID

LEFT JOIN (
    /* The following query will show the total number of studies by imaging area. */
    SELECT 
        PlacerFld2 AS "Type of Procedure", Count(*) AS "Count of Procedures"
    FROM 
        [order] o
    WHERE 
        lastmodifieddate BETWEEN '2017-01-01 00:00:00' AND '2017-12-31 23:59:59'
        AND SiteBridgeID = '1' /* Medical Imaging */
        AND OrderStatusID <> '4'
        AND placerfld2 IN ('CARD', 'CARM', 'CRFL', 'CT', 'I131', 'LACR', 'LAUS', 'NUCMED','OUTFM','PCCR','PETCT','PETR','PHMR','SACR','SAES','SAMR','SAUS','VRC','VRUS')
    GROUP BY 
        PlacerFld2) x ON x.[Type of Procedure] = PlacerFld2
--ORDER BY 
--    PlacerFld2 ASC

WHERE o.lastmodifieddate between '2017-01-01 00:00:00' AND '2017-12-31 23:59:59'
AND SiteBridgeID = '1' /* Medical Imaging */
AND OrderStatusID <> '4'
AND placerfld2 IN ('CARD','CARM','CRFL','CT','I131','LACR','LAUS','NUCMED','OUTFM','PCCR','PETCT','PETR','PHMR','SACR','SAES','SAMR','SAUS','VRC','VRUS')

GROUP BY Placerfld2, CITY, x.[Count of Procedures]
ORDER BY placerfld2 ASC, CITY ASC

The basic pattern here is:

SELECT... <your detail query>
FROM
    <your original detail tables>
    LEFT JOIN (<your summary query>) x ON x.<a field> = <original field>
WHERE.... <the rest of your detail query>
GROUP BY... <remember to add the new summary total to the GROUP BY clause>

I'll have to play around with this. The results came back with 0 in the field for percentage. — Daniël Cronk, Aug 01 '18 at 15:37
Try changing `x.[Count of Procedures]` to `CONVERT(NUMERIC(19,2), x.[Count of Procedures])`. I think this is simply an integer division problem. If you turn one of the values into a decimal then it will return a decimal result. At the moment both values are integers, so it gives an integer result. Your actual percentage could be 0.47, but this will be returned as 0. Another trick would be to multiply the result by `100.0`, which turns it into a percentage, AND makes the result a decimal. — Richard Hansell, Aug 02 '18 at 08:27
YES! That was exactly the issue. Thank you so much for your help with this. I'm not very advanced when it comes to SQL so thank you so very much!!!!! — Daniël Cronk, Aug 02 '18 at 15:42

SQL query yielding count and average

2 Answers2