total of items of a column - 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)

total of items of a column

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2002-12-16 : 15:42:37

I have a column in my database that I want to total on group. For example say I have a column of products_item, I want to total for product_item =xyz.

Also I have two tables 1 table has 4 columns and about 3000 records
The other has 64 columns and around 10,000 records. I'm joining the two on two key fields. Should I use a view and then use that for everything?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-16 : 15:49:42

SELECT product_item, SUM(Any_numeric_Column) as Total
FROM
table
GROUP BY product_item

Yes, a view is a good idea to join your two tables. Then, you can reference the view whenever you need to without worrying about joins.

- Jeff

Steve_LL
Starting Member

1 Post

Posted - 2002-12-17 : 15:48:39

For a grand total at the bottom, try:

SELECT 'Product' = (isnull(product_item,'Total')),
'Total' = SUM(Any_numeric_Column)
FROM table
GROUP BY product_item WITH CUBE

Steve

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-18 : 01:41:23

quote:

For a grand total at the bottom, try:

SELECT 'Product' = (isnull(product_item,'Total')),
'Total' = SUM(Any_numeric_Column)
FROM table
GROUP BY product_item WITH CUBE

Not a bad idea, but WITH CUBE/ROLLUP is tricky stuff, you might end up with a lot of results if u arent careful! adding just one more column to the query can easily increase the number of rows returned manyfold. If you just need the grand total, the COMPUTE statement is a better idea.

SELECT product_item,
SUM(Any_numeric_Column) AS Total
FROM table
GROUP BY product_item COMPUTE COUNT(product_item), SUM(SUM(any_numeric_column))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources