Hot Numbers - 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

Development Tools

ASP.NET

Hot Numbers

Author

Topic

laurag
Starting Member

4 Posts

Posted - 2007-01-16 : 10:08:41

Hey,

Today has been a bummer because i am usually faily confident with sql statements but i just can't seem to figure out the format. Basically i want data to be drawn from the same database and same table. Although i want to find out the 6 most common numbers in all 6 columns in my table. I've tried the count(*) method but it only seems to work on one column, not all of them and i can only serach for 1 number at the mo, not all the numbers.

Does anybody out there know how to count the numbers which come from all 6 columns(fields) and then display only the 6 highest amount of counted numbers. Therefore showing only the hot numbers.

So far i have mananged:

SELECT COUNT(*) AS Field3, Field4, Field5, Field6, Field7, Field8 FROM NumTable WHERE (Field3 = 1) AND (Field4 = 1) AND (Field5 = 1) AND (Field6 = 1) AND (Field7 = 1) AND (Field8 = 1) GROUP BY Field3, Field4, Field5, Field6, Field7, Field8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:14:44

Yes you can. Are always all columns filled with values, or is NULL a possibility?
Are the values in the columns always stored ascendin, that is, Col1 has a lower value than Col2, which has a lower value than Col3 and so on?

SELECT TOP 6 WITH TIES Col1, Col2, Col3, Col4, Col5, Col6
FROM NumTable
GROUP BY Col1, Col2, Col3, Col4, Col5, Col6
ORDER BY COUNT(*) DESC

Peter Larsson
Helsingborg, Sweden

laurag
Starting Member

4 Posts

Posted - 2007-01-16 : 10:21:15

Hello, No the numbers are never null and are always between 1-50 and they are in a random number. Thankyou for helping me. I just need to find out how many times each number occurs really so i can but it in a gridview

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:30:50

Will this be enough?

SELECT		TOP 6 WITH TIES x.ColValue
FROM		(
			SELECT Col1 AS ColValue FROM NumTable
			UNION ALL
			SELECT Col2 FROM NumTable
			UNION ALL
			SELECT Col3 FROM NumTable
			UNION ALL
			SELECT Col4 FROM NumTable
			UNION ALL
			SELECT Col5 FROM NumTable
			UNION ALL
			SELECT Col6 FROM NumTable
		) AS x
GROUP BY	x.ColValue
ORDER BY	COUNT(*) DESC

Peter Larsson
Helsingborg, Sweden

laurag
Starting Member

4 Posts

Posted - 2007-01-16 : 10:38:24

Hello,

Yeah ive just tried that sql statement and bummer the syntax of it was wrong, came up with errors. it did not like the UNION bit.
Thanks though
x

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 10:43:40

Why is that? Have you posted your question in the wrong forum?
Are you working with ASP against a MS ACCESS database?

Peter Larsson
Helsingborg, Sweden

laurag
Starting Member

4 Posts

Posted - 2007-01-16 : 11:09:19

Yep, defo using that mate. Making it with visual studio though

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 12:10:00

Great!
All is to satisfaction then?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 12:54:20