October 26, 2010 at 9:55 am
I'll start off with that I am not a sql programmer, but use TOAD in an analytical way. I am trying to create a query that sum's my tran_unit_qty, and groups by a few fields, then takes the top 5 with the same group by, but when I run this query I only get the top 5 sku's overall, not by store.
Any help you can provide with the below query would be appreciated:
SELECT *
FROM (SELECT kcfsr_fsi_line.store_number, kcfsr_fsi_line.sku_number,
SUM (kcfsr_fsi_line.tran_unit_qty)
FROM navistor.kcfsr_fsi_line PARTITION (week17)
WHERE (kcfsr_fsi_line.inv_move_code = 'PILF')
GROUP BY kcfsr_fsi_line.store_number, kcfsr_fsi_line.sku_number
ORDER BY 3 DESC)
WHERE ROWNUM <= 10
So in the end, by store i want to see the top 10 sku_number's shown.
October 26, 2010 at 11:03 am
I think this will do what you're trying to do.
;
WITH CTE AS (
-- first, take the subquery that groups all of the data
SELECT kcfsr_fsi_line.store_number,
kcfsr_fsi_line.sku_number,
SUMFIELD = SUM (kcfsr_fsi_line.tran_unit_qty)
FROM navistor.kcfsr_fsi_line PARTITION (week17)
WHERE (kcfsr_fsi_line.inv_move_code = 'PILF')
GROUP BY kcfsr_fsi_line.store_number, kcfsr_fsi_line.sku_number
), CTE2 AS
(
-- next, add a row number (RN) to it, starting over at each store
SELECT *,
RN = ROW_NUMBER() OVER (PARTITION BY store_number ORDER BY SUMFIELD DESC)
FROM CTE
)
SELECT *
FROM CTE2
WHERE RN <= 10;
If this doesn't work, please read the first link in my signature block, and then post CREATE TABLE statements to show what the table looks like, INSERT statements to populate the table with some sample data, and then show what the expected results are, based upon the sample data.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 26, 2010 at 11:20 am
Thank you for your response. I get an error when I run your code, it's "ORA-00923: FROM keyword not found where expected". I did read your link, but since I don't really understand what I'm writing to begin with, most of it was written with a query generator within TOAD, and I'm also not to create my own tables, I'm only able to query off tables already there.
October 26, 2010 at 11:30 am
Are you sure you're using SQL Server 2008????
The ORA-... error number indicates something different....
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply