Count repeated records and add total

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

SQL Server Development (2000)

Count repeated records and add total

Author

Topic

jelost
Starting Member

7 Posts

Posted - 2006-01-19 : 14:56:41

I have data that I would like to count then add the amounts in one of the columns: For Example
Item...ID...Times
Flour...2....7
Flour...2....3
Flour...2....5
Flour...4....2
Flour...4....4
Oil......1....3
Oil......1....2
Oil......1....6

So if it could do something like this:
Item...ID...Times
Flour...2....7
Flour...2....3
Flour...2....5
COUNT:3...TOTAL:15
Flour...4....2
Flour...4....4
COUNT:2...TOTAL:6
Oil......1....3
Oil......1....2
Oil......1....6
COUNT:3...TOTAL:11

Does this make any sense?
It ofcourse doesn't have to look like this.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 15:26:22

The closest that I can give u is (Assume - SQL Server 2000):

SELECT Item, [ID], Times
FROM UrTbl
ORDER BY Item
COMPUTE count(Item) BY Item, [ID]
COMPUTE sum(Times) BY Item, [ID]

jelost
Starting Member

7 Posts

Posted - 2006-01-19 : 15:44:36

Thank you for your quick response:
So I have made the data a bit simpler by joining the columns together using ----
SELECT item||'-'||to_char (id) AS combined, times
FROM urtbl
ORDER BY combined
--but when i add your two statements
COMPUTE count(Combined) BY Combined
COMPUTE sum(times) BY Combined

it says that my sql command is not properly ended. Any Ideas? I'm using sql navigator.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-19 : 15:45:49

quote:

I'm using sql navigator.

You'll want to post in an Oracle forum then as this site is for MS SQL Server. Check out dbforums.com.

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-19 : 15:49:43

Hey if u r working for an Oracle DB
u r in the wrong forum, this is mainly SQL server - read my answer carefully
U cannot use T-SQL in any other like PL/SQL

U may need to work on query analyzer for my knowledge

Please clarify, the database u r working with

jelost
Starting Member

7 Posts

Posted - 2006-01-19 : 16:01:24

I am sorry for my ignorance ...I did read your reply carefully..but i guess i just did not realize that there would be a difference in code.
I will make my post in the oracle forum (thanks tkizer).

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources