Query to Get Percentage of subcategory by Weekday

[EricRex]

Hey there,

This is complex, so I'll get there in steps:

This query:

SELECT St1.cWeekday, St1.EODAction, Count(St1.EODAction) AS NumActions
FROM StockTrades as St1
WHERE St1.EODAction<>""
GROUP BY St1.cWeekday, St1.EODAction
ORDER BY SWITCH(St1.cWeekday = 'Mon', 1,
                St1.cWeekday = 'Tue', 2,
                St1.cWeekday = 'Wed', 3,
                St1.cWeekday = 'Thu', 4,
                St1.cWeekday = 'Fri', 5), Count(St1.EODAction) DESC, St1.EODAction;

Generates a table of results with Weekday, category name, and count as you can see in attached screenshot.


My goal is to get the percentage of each category by weekday. So, if the Drop category has a count of 8 and Monday has a total of 32 EOD action counts, I should see an output of 25%. And each category would have its corresponding percentage.

In trying to get to the percentage, I created a query that does not work to simply output the total for each day in a separate column:

SELECT St1.cWeekday, St1.EODAction, Count(St1.EODAction) AS NumActions, (SELECT Count(St2.EODAction) FROM StockTrades WHERE St1.cWeekday = St2.cWeekday GROUP BY St2.cWeekday) as Total
FROM StockTrades as St1, StockTrades as St2
WHERE St1.EODAction<>"" AND St2.EODAction <> ""
GROUP BY St1.cWeekday, St1.EODAction
ORDER BY SWITCH(St1.cWeekday = 'Mon', 1,
                St1.cWeekday = 'Tue', 2,
                St1.cWeekday = 'Wed', 3,
                St1.cWeekday = 'Thu', 4,
                St1.cWeekday = 'Fri', 5), Count(St1.EODAction) DESC, St1.EODAction;

This query generates the error, Your query does not include the specified expression cWeekday as part of an aggregate function.

Appreciate any help on this.
Cheers,


Eric

[orange]

Can you provide a test database with data values matching your sample?
We really need the table and any queries you are dealing with.

[madpiet]

If all you want is Percent of Parent, this is stupid easy in Excel/DAX.

Total Actions:=SUM(Table1[NumActions])

Share of Day:=DIVIDE ( [Total Actions],
CALCULATE( [Total Actions], ALL('Table1'[EODAction]))
)

Or you could create a query that groups by Category
SELECT Category, SUM([Actions]) FROM <table> GROUP BY Category;

and then you would join that to the total (and leave out one or more grouping columns) then you'd join those two and divide.

[Welshgasman]

I see only 25 for Mon ?

[CJ_London]

Just do another aggregate query based on your first query but excluding categories, then join it to your first query on date.

[EricRex]

Just do another aggregate query based on your first query but excluding categories, then join it to your first query on date.

As a SQL n00B, I don't know how to do that.

[EricRex]

Can you provide a test database with data values matching your sample?
We really need the table and any queries you are dealing with.

I tried to upload a sample dB, but it doesn't appear in the attachments window. Verified that the extension is .accdb, but no go.

[Micron]

See how to attach files at the top of the page.

[madpiet]

As a SQL n00B, I don't know how to do that.

There are fancier ways of doing it, but since you're new, I'd go the easy route. So you break the question into two steps.
1. Get the total number of actions per Weekday, like this:

SELECT xlsData.cWeekday, Sum(xlsData.NumActions) AS [Total Actions]
FROM xlsData
GROUP BY xlsData.cWeekday;

2. Save that query ... (mine's called qtotWeekdayTotals)

3. Create a new query, add both the original data table and the qtotWeekdayTotals to the grid. Join on cWeekday column. Then just divide [NumActions] by [Total Actions], and finally, go into the properties of the new column and set the format to percentage

SELECT qtotWeekDayTotals.cWeekday, xlsData.EODAction, xlsData.NumActions, [NumActions]/[Total Actions] AS Share
FROM qtotWeekDayTotals INNER JOIN xlsData ON qtotWeekDayTotals.cWeekday = xlsData.cWeekday;

[EricRex]

There are fancier ways of doing it, but since you're new, I'd go the easy route. So you break the question into two steps.
1. Get the total number of actions per Weekday, like this:

SELECT xlsData.cWeekday, Sum(xlsData.NumActions) AS [Total Actions]
FROM xlsData
GROUP BY xlsData.cWeekday;

2. Save that query ... (mine's called qtotWeekdayTotals)

3. Create a new query, add both the original data table and the qtotWeekdayTotals to the grid. Join on cWeekday column. Then just divide [NumActions] by [Total Actions], and finally, go into the properties of the new column and set the format to percentage

SELECT qtotWeekDayTotals.cWeekday, xlsData.EODAction, xlsData.NumActions, [NumActions]/[Total Actions] AS Share
FROM qtotWeekDayTotals INNER JOIN xlsData ON qtotWeekDayTotals.cWeekday = xlsData.cWeekday;

Thanks for the starter code! For completeness, here are the working versions:

qryEODActionTotalsByWeekday:

SELECT cWeekday, Count(EODAction) AS TotalActions
FROM StockTrades
GROUP BY cWeekday
ORDER BY SWITCH(cWeekday = 'Mon', 1,
                cWeekday = 'Tue', 2,
                cWeekday = 'Wed', 3,
                cWeekday = 'Thu', 4,
                cWeekday = 'Fri', 5);

And qryEODActionTypesByWeekday:

SELECT [qryEODActionTotalsByWeekday].cWeekday, StockTrades.EODAction, Count(StockTrades.EODAction) AS ActionCount, Round(Count(StockTrades.EODAction)/[qryEODActionTotalsByWeekday].TotalActions*100,1) AS PctofTotal
FROM qryEODActionTotalsByWeekday INNER JOIN StockTrades ON [qryEODActionTotalsByWeekday].cWeekday=StockTrades.cWeekday
WHERE StockTrades.EODAction <>""
GROUP BY [qryEODActionTotalsByWeekday].cWeekday, StockTrades.EODAction, [qryEODActionTotalsByWeekday].TotalActions
ORDER BY SWITCH([qryEODActionTotalsByWeekday].cWeekday = 'Mon', 1,
                [qryEODActionTotalsByWeekday].cWeekday = 'Tue', 2,
                [qryEODActionTotalsByWeekday].cWeekday = 'Wed', 3,
                [qryEODActionTotalsByWeekday].cWeekday = 'Thu', 4,
                [qryEODActionTotalsByWeekday].cWeekday = 'Fri', 5), Count(StockTrades.EODAction) DESC , StockTrades.EODAction;

I'd love to know the fancier version of doing this. Have to admit, while I vaguely understand the JOIN concept, the details escape me.

Cheers,
Eric

[orange]

Please provide sample database.

[EricRex]

Please provide sample database.

Attached here:
SampleDB.accdb

Note for the Forum Admin: When I first tried to upload this 41MB sample DB, the upload dialog box spun as if processing but didn't show the file in the lower pane as uploaded. No error message, nothing. I then zipped it into a 5MB file and tried to upload which generated the "Exceeds max size" error. I was able to upload by compacting the DB.
There appears to be a issue with the upload error message when an ACCDB file exceeds the allowable size.

Cheers,
Eric

[orange]

Eric,

Well done!

Re uploading :
-always do compact/repair to remove garbage/ (deleted tables/queries/forms etc)
-zip to reduce overall size