|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
I HAVE A PROBLEM IN SQL
update, where, like, query to some extent. I have a problem: One Table "Master_Table" contains a column 'Calling Number" which begins with the region code followed by telephone number e.g. in 514567894, 51 is code of the region and 4567894 is the telephone number. The second table "Region_Codes" contains region names and codes of regions. Master_Table S.No.Calling_NumberTime_SecondsAmount_Dollars 151456789445624 22145678944556 342426858978978 44154566444515 55178912344510 64285274197532 7992789654325885 82174185294576 9419638527124 10992852852845644 Region_Codes S.No.Region_NameRegion_Code 1Rawalpindi51 2Karachi21 3Lahore42 4Faisalabad41 5Abbottabad992 I want to see the sum of "Amount_Dollars" for each region. Although I have been able to solve the problem by creating an additional column for region_Code in the "Master_Table" by update command and then executing a query grouped by region_code. This take huge time to update the table for each code. How can I see the sum of "Amount_Dollars" for each region. Regards, Muhammad Tariq tariq313***@gmail.com Your sample data is very difficult to read, since you have no delimiters.
My guess is that the following query may work select r.Region_Name , sum (m.Amount_Dollars) as Total from Region_Codes r join Master_Table m on m.No = r.Region_Code group by r.Region_Name -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau <tariq313***@gmail.com> wrote in message news:0c2a8e71-7dae-4dd9-a598-2c5cb59bc974@t26g2000prh.googlegroups.com... I have used SQL somewhat but I am not an expert. I can use select,update, where, like, query to some extent. I have a problem: One Table "Master_Table" contains a column 'Calling Number" which begins with the region code followed by telephone number e.g. in 514567894, 51 is code of the region and 4567894 is the telephone number. The second table "Region_Codes" contains region names and codes of regions. Master_Table S.No.Calling_NumberTime_SecondsAmount_Dollars 151456789445624 22145678944556 342426858978978 44154566444515 55178912344510 64285274197532 7992789654325885 82174185294576 9419638527124 10992852852845644 Region_Codes S.No.Region_NameRegion_Code 1Rawalpindi51 2Karachi21 3Lahore42 4Faisalabad41 5Abbottabad992 I want to see the sum of "Amount_Dollars" for each region. Although I have been able to solve the problem by creating an additional column for region_Code in the "Master_Table" by update command and then executing a query grouped by region_code. This take huge time to update the table for each code. How can I see the sum of "Amount_Dollars" for each region. Regards, Muhammad Tariq tariq313***@gmail.com I am not sure how the 'Calling Number' column is populated in your
master table, but why not split the region code at that time in separate column, instead of populating a column each time you need to group the data? If you cannot change that code, you can easily create a trigger on INSERT/UPDATE to perform the split. In addition, you can create a computed column which can be indexed.  
Other interesting topics
|
|||||||||||||||||||||||
