October 25, 2013 at 11:42 am
Hello, all. I have a query SELECT [Industry], [Naicstitle]
,[NumMatch] FROM [WDQI_DEED].[dbo].[q_Industry]
ORDER BY CASE WHEN Industry = '00' THEN Industry else null end DESC,
case WHEN Industry <> '00' THEN NumMatch ELSE NULL END DESC
; that produces the following results:
Industry Naicstitle NumMatch
0 All Industries 1025
62 Health Care and Social Assistance 650
61 Educational Services 148
44 Retail Trade 61
54 Professional and Technical Services 46
92 Public Administration 23
52 Finance and Insurance 22
55 Management of Companies and Enterprises 17
72 Accommodation and Food Services 14
Sorry, I wish this looked more like a grid, but the the first column is just codes, and the third has the values.
I would like to add a fourth column with the percents of the top row (all industries value = 1,025). Note that because of suppressions, the rows 2-11 will not necessarily add up to the top row. In Excel, this is easy with the formula =C2/C$2 but the solution in SQL eludes me. Can anyone help? Thank you,
October 25, 2013 at 11:55 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2013 at 11:57 am
You're looking for the FIRST_VALUE function.
DECLARE @testData TABLE
([Industry] VARCHAR(2)
,[Naicstitle] VARCHAR(50)
,[NumMatch] INT)
INSERT @testData (Industry,Naicstitle,NumMatch)
VALUES
('00', '', 1025)
,('62', '', 650)
,('61', '', 148)
,('44', '', 61)
,('54', '', 46)
,('92', '', 23)
,('52', '', 22)
,('55', '', 17)
,('72', '', 14)
SELECT
[Industry]
,[Naicstitle]
,[NumMatch]
,[NumMatch] / (1. * FIRST_VALUE([NumMatch]) OVER (PARTITION BY 1 ORDER BY Industry))
FROM
@testData
ORDER BY
CASE WHEN Industry = '00' THEN Industry
ELSE NULL
END DESC
,CASE WHEN Industry <> '00' THEN NumMatch
ELSE NULL
END DESC
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 25, 2013 at 12:02 pm
Thank you Jason. I had concocted a long CROSS APPLY method, but this FIRST_VALUE function is awesome. Thank you,
Amy
October 25, 2013 at 2:52 pm
Jason Selburg (10/25/2013)
You're looking for the FIRST_VALUE function.
DECLARE @testData TABLE
([Industry] VARCHAR(2)
,[Naicstitle] VARCHAR(50)
,[NumMatch] INT)
INSERT @testData (Industry,Naicstitle,NumMatch)
VALUES
('00', '', 1025)
,('62', '', 650)
,('61', '', 148)
,('44', '', 61)
,('54', '', 46)
,('92', '', 23)
,('52', '', 22)
,('55', '', 17)
,('72', '', 14)
SELECT
[Industry]
,[Naicstitle]
,[NumMatch]
,[NumMatch] / (1. * FIRST_VALUE([NumMatch]) OVER (PARTITION BY 1 ORDER BY Industry))
FROM
@testData
ORDER BY
CASE WHEN Industry = '00' THEN Industry
ELSE NULL
END DESC
,CASE WHEN Industry <> '00' THEN NumMatch
ELSE NULL
END DESC
I agree with you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply