Posts

Showing posts from June, 2009

Using Group by with Cube in SQL Server

In Microsoft SQL Server normally when you use aggregation function you must use Group By clause to get the Total/Average So on. But by using Group by you can get the aggregation only (Total/Avg…etc) based on the columns which you are giving in Group By clause. But if you want get the subtotals; you can use Cube, rollup clause with Group By clause. Cube clause would give the subtotal for all possible matching like summary for all specified columns whatever you mentioned in group by clause Main different between Cube and Rollup is, Group BY with Cube clause would give All the Subtotal as summary. In the shown example, I need to get the total and subtotal of NoofBooks based on BCode and NoofLeaves but I can only get total based on those columns if I use Group By clause only. For that I can use cube clause with Group By clause. When we use cube clause it would give total of NoofBooks for each BCode and NoofLeaves , subtotal for each BCode , Give the Subtotal s

Get the Subtotal, Total using Group By with Rollup in SQL Server

In Microsoft SQL Server normally when you use aggregation function you must use Group By clause to get the Total/Average So on. But by using Group by you can get the aggregation only (Total/Avg…etc) based on the columns which you are giving in Group By clause. But if you want get the subtotals; you can use rollup clause with Group By clause. Rollup clause would give the subtotal specified columns whatever you mentioned in group by clause. In the shown example, I need to get the total and subtotal of NoofBooks based on BCode and NoofLeaves but I can only get total based on those columns if I use Group By clause only. For that I can use rollup clause with Group By clause. When we use rollup clause it would give total of NoofBooks for each BCode and NoofLeaves , subtotal for each BCode and Grand Total of NoOfBooks Here you can notice that when its give the subtotal for each BCode , NoofLeaves columns filled NULL values as it does not have any values to retur

Use of SOUNDEX() Function in Oracle

You use SOUNDEX(x) to get a string containing the phonetic representation of x. This lets you compare words that sound alike in English but are spelled differently. The following query retrieves the last_name column from the customers table where last_name sounds like “whyte”: (Pronouncing in same way but spelling different ) SELECT last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('whyte'); LAST_NAME ---------- White The next query gets last names that sound like “bloo”: SELECT last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('bloo'); LAST_NAME ---------- Blue

How to search wildcards character in string

If you need to search for actual underscore or percent characters in a string, you can use the ESCAPE option to identify those characters. For example, ' %\%%' ESCAPE '\' The character after the ESCAPE tells the database how to differentiate between characters to search for and wildcards, and in the example the backslash character (\) is used. The first % is treated as a wildcard and matches any number of characters; the second % is treated as an actual character to search for; the third % is treated as a wildcard and matches any number of characters. The following query uses the promotions table, which contains the details for products being discounted by the store . The query uses the LIKE operator to search the name column of the promotions table for the values whatever starts or ends with '%' character. SELECT name FROM promotions WHERE name LIKE '% \% % ' ESCAPE '\'; The results would be as shown below, NAME ------------------------------ 1