InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:37:40 GMT20http://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspx[quote][b]AndrewSQLDBA (1/17/2013)[/b][hr]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?[/quote]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.[code="sql"]--===== 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;[/code]Fri, 18 Jan 2013 07:53:42 GMTSteve Thompson-454462http://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxThank 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 SQLDBAThu, 17 Jan 2013 12:59:32 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspx[quote][b]GilaMonster (1/17/2013)[/b][hr]Also, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do.[/quote]Especially since WITH CUBE and WITH ROLLUP are being deprecated.DrewThu, 17 Jan 2013 10:16:33 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxAlso, since this is SQL 2008, have a read up on Grouping Sets. They allow a lot more flexibility than cube and rollup do.Thu, 17 Jan 2013 05:06:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxIf 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.Thu, 17 Jan 2013 04:49:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxAnthonyThanks is perfect.Thank You. I greatly appreciate that. I never thought to use cube.Andrew SQLDBAThu, 17 Jan 2013 04:47:15 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxSomething like the following?[code="sql"]--===== 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;[/code]Thu, 17 Jan 2013 01:53:01 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1408130-392-1.aspxHello 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:[code="sql"]--===== 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;[/code]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 SQLDBAWed, 16 Jan 2013 20:52:03 GMTAndrewSQLDBA