## Something Most Likely Simple

 Author Message AndrewSQLDBA Ten Centuries Group: General Forum Members Points: 1008 Visits: 3427 Hello EveryoneI have a rather odd query that I am trying to figure out the best way to code. I am going to try and explain the best way possible. I want to be able to do this without incorporating a loop.I have a table that stores patient information about Diagnosis. There are three distinct hospital locations. Lets Call them LA, NY and Indy. There are three CodeNumbers for each Location : 30.1, 40.1 and 50.1Now, what I am after is the count for each CodeNumber, per Each Location per each day.These are the counts of each code per each location per each day.Counts:LA = 402 = TotalLA - 30.1 = 57LA - 40.1 = 278LA - 50.1 = 67NY = 82 = TotalNY - 30.1 = 13NY - 40.1 = 55NY - 50.1 = 14Indy = 142 = TotalIndy - 30,1 = 19Indy - 40.1 = 77Indy - 50.1 = 46The way the data is stored is like this:`--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL DROP TABLE #Diagnosis--===== Create the test table with CREATE TABLE #Diagnosis ( RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , DateDataRepresents Date , CodeNumber DECIMAL(4,1) , Location varchar(5) )INSERT INTO #Diagnosis(DateDataRepresents, CodeNumber, Location)SELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY'SELECT DateDataRepresents, CodeNumber, Location FROM #Diagnosis;DROP TABLE #Diagnosis;`etc.........The table has approx half a million rowsWhat is the most efficient way to get the counts?Thank You in advance for your help, suggestions and adviceAndrew SQLDBA anthony.green SSCertifiable Group: General Forum Members Points: 6108 Visits: 6080 Something like the following?`--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL DROP TABLE #Diagnosis--===== Create the test table with CREATE TABLE #Diagnosis ( RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , DateDataRepresents Date , CodeNumber DECIMAL(4,1) , Location varchar(5) )INSERT INTO #Diagnosis(DateDataRepresents, CodeNumber, Location)SELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY';with cte as(SELECT DateDataRepresents, Location , CodeNumber,COUNT(RowID) AS CountFROM #DiagnosisGROUP BY DateDataRepresents, CodeNumber,Locationwith cube)select DateDataRepresents, Location, ISNULL(CONVERT(VARCHAR,CodeNumber),'Total') AS CodeNumber, Countfrom cte where datedatarepresents is not null and location is not null order by Location,CodeNumber,DateDataRepresentsDROP TABLE #Diagnosis;` Want an answer fast? Try hereHow to post data/code for the best help - Jeff ModenWhen a question, really isn't a question - Jeff SmithNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawCrossTabs-Part1 & Part2 - Jeff ModenSQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola HallengrenManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger AndrewSQLDBA Ten Centuries Group: General Forum Members Points: 1008 Visits: 3427 AnthonyThanks is perfect.Thank You. I greatly appreciate that. I never thought to use cube.Andrew SQLDBA anthony.green SSCertifiable Group: General Forum Members Points: 6108 Visits: 6080 If also not familiar lookup WITH ROLLUP, its another one to look at along with WITH CUBE, both produce different outputs so need to ensure you choose the right one, ROLLUP wont work in this instance but CUBE does. Want an answer fast? Try hereHow to post data/code for the best help - Jeff ModenWhen a question, really isn't a question - Jeff SmithNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawCrossTabs-Part1 & Part2 - Jeff ModenSQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola HallengrenManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger GilaMonster SSC-Forever Group: General Forum Members Points: 47427 Visits: 44405 Also, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do. Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass drew.allen SSCrazy Group: General Forum Members Points: 2687 Visits: 9967 GilaMonster (1/17/2013)Also, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do.Especially since WITH CUBE and WITH ROLLUP are being deprecated.Drew J. Drew AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.How to Post Performance ProblemsMake sure that you include code in the appropriate IFCode tags, e.g. [code=sql][/code]. You can find the IFCode tags under the INSERT options when you are writing a post. AndrewSQLDBA Ten Centuries Group: General Forum Members Points: 1008 Visits: 3427 Thank You EveryoneOk, I have been reading up on Grouping Sets, but I still am not getting it. How would one go about writing a query to work correctly with the same data that was given and not use Cube or Rollup?Thanks in advanceAndrew SQLDBA Steve Thompson-454462 SSC Veteran Group: General Forum Members Points: 289 Visits: 2057 AndrewSQLDBA (1/17/2013)Thank You EveryoneOk, I have been reading up on Grouping Sets, but I still am not getting it. How would one go about writing a query to work correctly with the same data that was given and not use Cube or Rollup?You could always "roll your own" Totals. Performs fine with the small sample data set; if your production set is much larger, you'd want to compare metrics to see if this code scales.`--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL DROP TABLE #Diagnosis--===== Create the test table with CREATE TABLE #Diagnosis ( RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , DateDataRepresents Date , CodeNumber DECIMAL(4,1) , Location varchar(5) )INSERT INTO #Diagnosis(DateDataRepresents, CodeNumber, Location)SELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 40.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 50.1, 'NY' UNION ALLSELECT '2012-12-16', 30.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'Indy' UNION ALLSELECT '2012-12-16', 40.1, 'NY' UNION ALLSELECT '2012-12-16', 40.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'NY' UNION ALLSELECT '2012-12-16', 50.1, 'LA' UNION ALLSELECT '2012-12-16', 30.1, 'Indy' UNION ALLSELECT '2012-12-16', 50.1, 'NY';with cte as( SELECT DateDataRepresents, Location, CodeNumber, COUNT(RowID) AS Count_nb FROM #Diagnosis GROUP BY DateDataRepresents, CodeNumber, Location)-- Totals by CodeNumberSELECT DateDataRepresents, Location, CAST(CodeNumber as CHAR(4)) AS CodeNumber, Count_nbFROM cte WHERE DateDataRepresents IS NOT NULL AND Location IS NOT NULL UNION ALL-- Totals by LocationSELECT DateDataRepresents, Location, 'Total' AS CodeNumber, SUM(Count_nb) AS Count_nbFROM cte WHERE DateDataRepresents IS NOT NULL AND Location IS NOT NULL GROUP BY DateDataRepresents, Location ORDER BY Location, CodeNumber, DateDataRepresentsDROP TABLE #Diagnosis;`