views:

148

answers:

4

I need to add a row dynamically in SQL after the Marketer number changes with the header "Marketer Total" that should add only the "Total" column. For example, after the last row of Marketer 22 row, there should be "Marketer Total" and then under the Total column should be 1804. The same should occur after the last row of Marketer 500.

See image at http://agricam.net/test.gif

Current Query:

select Marketer, SubMarketer, Grade, Total, Convert(varchar,Date,101)[Date] from SomeTable where Date >= '2/25/2009' and Date < '2/26/2009' and Marketer in ('22','500') group by SubMarketer,Grade,Marketer, Date , total order by Marketer

Thanks.

+6  A: 

see Using ROLLUP to aggregate data in SQL

implementation:

DECLARE @SOMETABLE TABLE (SUBMARKETER INT, GRADE CHAR, MARKETER INT, 
  DATE DATETIME, TOTAL INT)
INSERT INTO @SOMETABLE
SELECT 1415, 'A', 22, '02/25/2009', 26 UNION
SELECT 1415, 'B', 22, '02/25/2009', 93 UNION
SELECT 1415, 'C', 22, '02/25/2009', 1175 UNION
SELECT 1415, 'D', 22, '02/25/2009', 510 UNION
SELECT 1169, 'B', 500, '02/25/2009', 1 UNION
SELECT 1169, 'C', 500, '02/25/2009', 3 UNION
SELECT 1393, 'C', 500, '02/25/2009', 2 UNION
SELECT 2, 'B', 500, '02/25/2009', 5 UNION
SELECT 2, 'C', 500, '02/25/2009', 111 UNION
SELECT 2, 'D', 500, '02/25/2009', 18 

SELECT 
  CASE WHEN SUBMARKETER IS NULL THEN 'Marketer Total' 
    ELSE CONVERT(VARCHAR, MARKETER) END MARKETER, 
    SUBMARKETER, GRADE, TOTAL, DATE 
  FROM (
    SELECT MARKETER, SUBMARKETER, GRADE, SUM(TOTAL) AS TOTAL, 
    CONVERT(VARCHAR,DATE,101)[DATE] 
    FROM @SOMETABLE 
    WHERE DATE >= '2/25/2009' AND DATE < '2/26/2009' 
     AND MARKETER IN ('22','500') 
    GROUP BY MARKETER, SUBMARKETER, GRADE, DATE WITH ROLLUP
)M 
WHERE M.MARKETER IS NOT NULL 
AND NOT (SUBMARKETER IS NOT NULL AND DATE IS NULL)

attention: this will work fine if MARKETER, SUBMARKETER and DATE columns are NOT NULL. If there will be NULL values of theese fields in table, that will became an issue to filter out useless grouping.

Max Gontar
ROLLUP() and PARTITION() using the Marketer number should help you. Its a complex one though. Good luck.
achinda99
I was playing with that idea, but i can't get it to do the right thing.
egjoni
A: 

Create a trigger on the table that calculates the total when a record is inserted. That way, you can verify you have the lastest total any time there is movement in the database.

Gregory A Beamer
If there are millions and millions of row, ths might be a good strategy to use triggers to maintain an aggregate table. But it would have to be a lot. You'd also have to trigger a recalcu of the totals on update and delete triggers as well.
ChrisLoris
I think the best way would be to go through Grouping/ROLLUP, It's just a hard time to get it to show exactly what i need.
egjoni
I don't have an issue with either summing or denormalization. Either can be a valid method of solving the problem. As you have stated, it really depends.
Gregory A Beamer
A: 

Coldice's answer is the correct one. Unless you have a compelling reason to store aggregate data in the table, the query engine is sufficiently powerful enough to do this for you. Can you explain why you need this row in the table? Is it for display purposes? You can use the ROLLUP function to put the aggregate results in a query or you can run a separate query a such to sow the totals:

SELECT marketer, SUM(total) FROM TablenameHere GROUP BY marketer
ChrisLoris
It's a report...therefore the data needs to be shown broken down
egjoni
Most reporting engines support group totals. What report writer are you using?
Ken White
I am simply writing a query in SQL.
egjoni
Make up your mind. :-) The comment above mine says "It's a report". It's either a report or a SQL query. If it's a report, as your comment says, *something* is outputting it, and that *something* should be able to total groups. If it's simply SQL, ROLLUP is probably your best bet.
Ken White
A: 

I believe you could also use COMPUTE SUM

MikeW
I've tried your proposition, but it seems like total for each marketer was returned in separate resultset
Max Gontar
As the link says, `COMPUTE` is deprecated -- use `ROLLUP` instead.
Jon Seigel