Forum Discussion

MinuteImprovement1085's avatar
MinuteImprovement1085
Copper Contributor
Jun 01, 2023

Query question

have a query which the output is this

| Milestone | Counts | | Order | | -------- | -------- | | -------- | | Entrance | 100 | | 01 | | Lunch | 50 | | 02 | | Closings | 25 | | 03 |

The query is like so

SELECT
CASE WHEN groupname = 'Entrance' THEN '01'
     WHEN groupname = 'Lunch' THEN '02' 
     WHEN groupname = 'Closings' THEN '03' END AS "MilestoneOrder"
,groupname AS "Milestone"
,COUNT(DISTINCT personid) AS "Counts"
FROM PersonCounts
WHERE DateComparison IN('firstenterbeforetransact', 'firstentersamedayastransact')
GROUP BY groupname
ORDER BY MilestoneOrder

I want to try to add a line in my code which will help show the Milestone Percentages for each milestone. For example: Entrance % would be 100/100 = 100%, Entrance -> Lunch would be 50/100 = 50%, and lastly Entrance -> Closings would be 25/100 = 25%. I am struggling to figure this calculation out.

I tried adding this line here, but I kept getting an error: ,COUNT(DISTINCT personid) / COUNT(lpersonid WHERE groupname = 'Entrance') AS "ConversionPercentage"

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

     


    have a query which the output is this

    MinuteImprovement1085 , that "output" is nearly unreadable.

    Please post table design as DDL, some sample data as DML statement and the expected result.

Share

Resources