Sub Total Using Rollup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sub Total Using Rollup

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-20 : 08:40:39

davrok writes "I have the following sql:

SELECT CASE WHEN (GROUPING(dbo.products.product_id) = 1 and GROUPING(dbo.terminal_stores.ters_CONTINENT_ID) = 1)
THEN 9999999 ELSE dbo.products.product_id END AS product_ID, sum(dbo.stock_contract.kilos) as kilos,
max(dbo.continents.continent) as continent, max(dbo.products.product_name) as product_name
FROM dbo.STOCK_CONTRACT INNER JOIN
dbo.TERMINAL_CLIENTS_MAPPING ON dbo.STOCK_CONTRACT.TRADER_ID = dbo.TERMINAL_CLIENTS_MAPPING.TERMINAL_TRADER_ID AND
dbo.STOCK_CONTRACT.TERMINAL_HO_GUID = dbo.TERMINAL_CLIENTS_MAPPING.TERMINAL_HEAD_OFFICE_GUID INNER JOIN
dbo.TERMINAL_STORES ON dbo.STOCK_CONTRACT.TERMINAL_ID = dbo.TERMINAL_STORES.TERMINAL_STORE_REF_ID AND
dbo.STOCK_CONTRACT.TERMINAL_HO_GUID = dbo.TERMINAL_STORES.TERMINAL_HO_GUID INNER JOIN
dbo.TERMINAL_PRODUCTS_MAPPING ON
dbo.STOCK_CONTRACT.PRODUCT_ID = dbo.TERMINAL_PRODUCTS_MAPPING.TERMINAL_PRODUCT_CODE AND
dbo.STOCK_CONTRACT.TERMINAL_HO_GUID = dbo.TERMINAL_PRODUCTS_MAPPING.TERMINAL_HEAD_OFFICE_GUID INNER JOIN
dbo.PRODUCTS ON dbo.TERMINAL_PRODUCTS_MAPPING.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID INNER JOIN
dbo.CONTINENTS ON dbo.TERMINAL_STORES.TERS_CONTINENT_ID = dbo.CONTINENTS.CONTINENT_ID
WHERE (dbo.TERMINAL_CLIENTS_MAPPING.CLIENT_HEAD_OFFICE_GUID = '{DDEA952A-8E59-4353-9965-BD555E5B6579}')
GROUP BY dbo.terminal_stores.ters_CONTINENT_ID, dbo.products.product_id WITH ROLLUP

which produces the following results:

prodid kilos continent product
320 10 Europe Apples
320 12 Europe Apples
346 30 Europe Bananas
1711 5 Europe Grapes
NULL 57 Europe Apples
320 30 Asia Apples
346 15 Asia Bananas
346 15 Asia Bananas
NULL 60 Asia Bananas
9999999 117 Asia Bananas

As you can see the two NULL columns are the sum of ALL products
and 999999 column is the sum of both NULL column totals.

What i am trying to achieve as results is this, which is breakdown of the products by continent with a total of that product after each:

Continent Product kilos
Asia Apples 30
Europe Apples 22
Total 52
Asia Bananas 30
Europe Bananas 30
Total 60
Europe Grapes 5
Total 5

Thanks in advance for any help...."

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-20 : 10:44:27

You are trying to do reporting in SQL Server. SQL Server is a data server, not a reporting tool. Use Crystal Reports, MS Access, Active Reports, or some other tool that is appropriate for this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources