April 8, 2003 at 11:21 am
Is it possible to select a different number of records based on different groups. For example, I would only like to see the last 4 records from this group and the last 3 records from another group.
Please see example below:
/* --- cut here -- */
USE TEMPDB
BEGIN TRAN
SET NOCOUNT ON
CREATE TABLE MY_GROUPS(THE_ID INT IDENTITY(1,1), THE_GROUP_NAME VARCHAR(10), NUM_OF_RECORDS INT);
INSERT INTO MY_GROUPS(THE_GROUP_NAME, NUM_OF_RECORDS) VALUES('Group A', 3);
INSERT INTO MY_GROUPS(THE_GROUP_NAME, NUM_OF_RECORDS) VALUES('Group B', 4);
CREATE TABLE MY_TABLE(THE_ID INT IDENTITY(1,1), THE_GROUP_ID INT, THE_VALUE DECIMAL(5,2));
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 5);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 10);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 15);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 20);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 25);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 5);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 10);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 15);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 20);
INSERT INTO MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 25);
SET NOCOUNT OFF
ROLLBACK
/* --- cut here -- */
In my report, I would like to show the sum of the last 3 records for group A and last 4 records for group B (as indicated in the MY_GROUPS table) as shown below:
THE_ID THE_GROUP_NAME TOTAL
====== ============== =====
1 Group A 60.00
2 Group B 70.00
Is it possible to do this query?
Thanks in advance,
Billy
April 8, 2003 at 11:29 am
If you know the groups and the # of records to sum for each group, the easiest way that comes to mind is with a UNION (actually a UNION ALL since you don't have to rely on SQL Server to make the rows unique... they already are).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 8, 2003 at 11:36 am
Thanks for your response.
However, the number of groups is unknown. Therefore, generating a dynamic query (ie. piecing it together the SQL statement) may not be the best solution for me.
April 8, 2003 at 12:53 pm
quote:
...However, the number of groups is unknown. Therefore, generating a dynamic query (ie. piecing it together the SQL statement) may not be the best solution for me...
While I think this is a perfect example of how NOT to use T-SQL to do things a real programming language should, this was a fun little exercise...
SET NOCOUNT ON
CREATE TABLE #MY_GROUPS(THE_ID INT IDENTITY(1,1), THE_GROUP_NAME VARCHAR(10), NUM_OF_RECORDS INT)
INSERT INTO #MY_GROUPS(THE_GROUP_NAME, NUM_OF_RECORDS) VALUES('Group A', 3)
INSERT INTO #MY_GROUPS(THE_GROUP_NAME, NUM_OF_RECORDS) VALUES('Group B', 4)
CREATE TABLE #MY_TABLE(THE_ID INT IDENTITY(1,1), THE_GROUP_ID INT, THE_VALUE DECIMAL(5,2));
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 5)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 10)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 15)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 20)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(1, 25)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 5)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 10)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 15)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 20)
INSERT INTO #MY_TABLE(THE_GROUP_ID, THE_VALUE) VALUES(2, 25)
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N''
SELECT @SQL = @SQL + N'SELECT THE_GROUP_NAME, SUM(THE_VALUE) AS SUM_VALUES ' +
'FROM (SELECT TOP ' +
CONVERT(VARCHAR(3), NUM_OF_RECORDS) +
' THE_GROUP_ID, THE_VALUE FROM #MY_TABLE WHERE THE_GROUP_ID = ' +
CONVERT(VARCHAR(14), THE_ID) +
' ORDER BY THE_ID DESC) AS DT1 ' +
'INNER JOIN #MY_GROUPS ON THE_GROUP_ID = THE_ID ' +
'GROUP BY THE_GROUP_NAME' + CHAR(13)
FROM #MY_GROUPS
EXEC(@SQL)
DROP TABLE #MY_GROUPS
DROP TABLE #MY_TABLE
SET NOCOUNT OFF
Results:
THE_GROUP_NAME SUM_VALUES
-------------- ----------------------------------------
Group A 60.00
THE_GROUP_NAME SUM_VALUES
-------------- ----------------------------------------
Group B 70.00
April 8, 2003 at 1:51 pm
Thanks for your response.
However, wouldn't it be better to build this report using relational programming rather than something else like procedural programing? All we are trying to do is select rows from two columns and summarize it.
But, if it is impossible to write this report in relational programming style, then we have to look for workarounds.
Billy
April 8, 2003 at 1:53 pm
quote:
...But, if it is impossible to write this report in relational programming style, then we have to look for workarounds...
???
I just posted the code to do the report...
Not quite sure what you are getting at. Please elaborate.
Thanks,
Jay
April 8, 2003 at 7:45 pm
Hi Jay:
I thought that you were trying to say that this report should NOT be done with relational programming style. However, just rereading your message it appears I am mistaken (my apologies) All I wanted to do was say I wanted to do it the report in relational programming style first before trying it any other way. However, I already typed up my response so I will post it...
Let's say for example, using the NORTHWIND database, I have to do a report that lists out all the freight amounts of the orders of all the employees in the database. (there are 9 employees in the EMPLOYEES table).
If I use procedural programming style, I would use this:
USE NORTHWIND
SET NOCOUNT ON
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 1 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 2 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 3 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 4 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 5 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 6 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 7 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 8 GROUP BY EMPLOYEEID;
SELECT EMPLOYEEID, SUM(FREIGHT) AS TOTAL_FREIGHT FROM ORDERS WHERE EMPLOYEEID = 9 GROUP BY EMPLOYEEID;
SET NOCOUNT OFF
which I could get by using looping through all the emplyees in the EMPLOYEE table using something like:
FOR X = 1 TO 9
SELECT....
NEXT
However, if I were to use relational programming style, I would use this:
SELECT O.EMPLOYEEID, ISNULL(SUM(FREIGHT),0) AS TOTAL_FREIGHT
FROM ORDERS O LEFT JOIN EMPLOYEES E ON O.EMPLOYEEID = E.EMPLOYEEID
GROUP BY O.EMPLOYEEID
ORDER BY O.EMPLOYEEID;
Both programming styles achieve the same goal but one is more efficient than the other.
The report I am trying to build seems impossible in relational programming style so I may have to piece together the SQL statement (ie. use procedural programming style). Not preferred but it seems like I don't have any other choice.
Kind regards,
Billy
April 9, 2003 at 6:28 am
quote:
...The report I am trying to build seems impossible in relational programming style so I may have to piece together the SQL statement (ie. use procedural programming style). Not preferred but it seems like I don't have any other choice....
This is what I understood from your previous emails, therefore I posted you the code to do the report per the spec you gave in your initial posting. If you want a single report (header rows not repeating), try this:
CREATE TABLE #MY_REPORT_TABLE(THE_GROUP_NAME VARCHAR(10), SUM_VALUE DECIMAL(7,4));
DECLARE @SQL NVARCHAR(4000)
SET @SQL = N''
SELECT @SQL = @SQL + N'INSERT INTO #MY_REPORT_TABLE SELECT THE_GROUP_NAME, SUM(THE_VALUE) AS SUM_VALUES ' +
'FROM (SELECT TOP ' +
CONVERT(VARCHAR(3), NUM_OF_RECORDS) +
' THE_GROUP_ID, THE_VALUE FROM #MY_TABLE WHERE THE_GROUP_ID = ' +
CONVERT(VARCHAR(14), THE_ID) +
' ORDER BY THE_ID DESC) AS DT1 ' +
'INNER JOIN #MY_GROUPS ON THE_GROUP_ID = THE_ID ' +
'GROUP BY THE_GROUP_NAME' + CHAR(13)
FROM #MY_GROUPS
EXEC(@SQL)
SELECT * FROM #MY_REPORT_TABLE
On a further note, I don't think anyone would disagree with you that a relational command (sometimes referred to as SET-based calculations) would be faster than a procedural command attempting the same thing with a loop.
However, in this case, unless of course somebody identifies a way of doing it, there is no way to do a true SET-based command to accomplish the entire report. The reason is simply because of the variable TOP CLAUSE. If it were known that you wanted a specific number of the top items for each group, then it would be possible...
But perhaps I'm wrong. Joe Celko has written a number of books on advanced SQL techniques. Perhaps someone out there knows a way...
Cheers,
Jay
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply