You can see by the results that taking time to better understand your options and altering your select statement you can not only get percent of total using Selects but do it in a very concise and optimized way. I used about 1,500 records and with the help of Toad for SQL here are the results. There is a major improvement in performance as well as it is easier to understand. Is this possible I understand that it is a 'Comma' separated file, but our requirement needs this change to be made. I am facing a strange problem where I need to change the delimiter in the CSV file. By changing the Select to this instead….Ĭast((count(Age) over(partition by age)/(count(age) over()*1.000)) as float)*100 as Percentage I am using sql server 2005, Reporting services. But using Over(Partition By …) you can achieve some pretty amazing performance gains. This will give you the right answer and is a pretty common way I have seen a select statement like this written. Select Age,count(*) as num, as total from TempTable tb1 Group by Age) ee Select Age, num, Total, (cast(num as float)/Total)*100 as PctTotal from ( The SQL PARTITION BY clause returns all rows satisfied by the SELECT statement along with an additional column that shows aggregated data. The SQL query also specifies the non-aggregated columns such as CustomerName in the SELECT statement. Set = (select count(*) as totalrec from TempTable) Therefore, we specify the SQL statement as OVER(PARTITION BY City). INSERT INTO TempTable (, Age) VALUES (‘Jake’,18) INSERT INTO TempTable (, Age) VALUES (‘Laura’,19) INSERT INTO TempTable (, Age) VALUES (‘Susan’,20) INSERT INTO TempTable (, Age) VALUES (‘Jennifer’,35) INSERT INTO TempTable (, Age) VALUES (‘Linda’,37) INSERT INTO TempTable (, Age) VALUES (‘Tom’,37) The Oracle database accepts the data and executes the INSERT statement to store the data. INSERT INTO TempTable (, Age) VALUES (‘Sarah’,37) SQLLoader is invoked when you specify the sqlldr command and. INSERT INTO TempTable (, Age) VALUES (‘Heather’,41) INSERT INTO TempTable (, Age) VALUES (‘Lin’,40) INSERT INTO TempTable (, Age) VALUES (‘Bill’,19) INSERT INTO TempTable (, Age) VALUES (‘Steve’,18)
INSERT INTO TempTable (, Age) VALUES (‘Jeff’,19) INSERT INTO TempTable (, Age) VALUES (‘David’,34) Here are the details.ĬREATE TABLE TempTable( varchar(20),Age Int) Using this data I generated a sample table with data to perform an optimization exercise.