Counting the distinct totals of a row

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)

Counting the distinct totals of a row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-15 : 08:41:10

Steve writes "Hi guys!

SQL7 SP2, WIN2K SP2, ASP.NET

( First off! awesome site, have been a regular visitor for a while now. )

Now my little question,

I have a table listed log's from various sources. these are simple text fields.

The table is in this format

<id> <value>
1 DOOR
2 MAT
3 MAT
4 WINDOW
5 DOOR
< and so on >

So I could end up with 50 row's of doors etc. What I am after is the sum of each distinct in the value col.

So I have data like DOOR(14) MAT(11) WINDOW(1), I want to show the data as a pie chart using some ASP.NET controls and as such need to return the distinct name for the label and the values for the PIE data for the array.

Can you help at all?

Thanks

Steve"

Nazim
A custom title

1408 Posts

Posted - 2002-04-15 : 09:04:41

select value,count(1)
from tablename
group by value

will give you distinct values with the no. of occurences. you can use it to plot your graph.

--------------------------------------------------------------

SteveW
Starting Member

2 Posts

Posted - 2002-04-15 : 09:59:00

quote:

select value,count(1)
from tablename
group by value
will give you distinct values with the no. of occurences. you can use it to plot your graph.

Thanks, this worked a treat. I have the names and the values. I am now going about working on the data returned. I am after from this now the top postings so I can do a "best" or "top" but this is something I should work out rather than bug ppl :P

Again cheers.

Steve

SteveW
Starting Member

2 Posts

Posted - 2002-04-15 : 11:09:26

Just as a FYI, I ended up with

SELECT TOP 20 Signal, Number=count(1)
from LogTable where
Signal is not null
GROUP BY Signal
ORDER BY Number desc

Which produced the top 20 ordered correctly and worked a treat with the charting software.

Thanks again.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources