SQL Count Help - 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

General SQL Server Forums

New to SQL Server Programming

SQL Count Help

Author

Topic

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-13 : 11:20:20

Hello,

I am new to Programming in SQL and I was wondering if there was anyone kind enough to help me. I would like an SQL statement to count groups of consecutive numbers.

Essentually I am Trying to Write a statement to count the amount of consecutive numbers missing from the ITEMMASTER Table. So we can reuse some of those ITEMCODE 's again. The Following Coulumn is the ITEMCODES that are Currently UNUSED/ MISSING. I want to count how many consecutive numbers in the column and put them into groups based on the COUNT of consecutive numbers. I want there two be four Groups total that they would go in based on the COUNT. the groups would be : COUNT 22 or greater; Count 18-21; Count 12-17; Count 5-11

ITEMCODE

1
2
3
4
6
7
8
9
10
11
12
13
14
15
16
17
18
20
21
22
23
24

I would Like the out put to look something like this:

Count 12-17 Count 5-11

Start End Count Start End Count

6 18 13 20 24 5

NOTE: large counts only end up in the proper range and not also in the smaller range as well

WebFOCUS 7.6

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-13 : 11:22:55

Understand this

Rows of Data are not related to each other, unless you have Columns that have Id's that reference back to a different row. This would be considered hierarchal data.

IF you want to live by the physical order of data in a database, your reslts will always be suspect.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-13 : 11:29:58

Look Like This

......Count 22_+.....Count.18-21......Count.12-17.....Count.5-11....
...Start.End.Count..Start.End.Count..Start.End.Count..Start.End.Count
......................................6....18...13.....20....24...5..

WebFOCUS 7.6

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-13 : 13:34:24

look at what?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-13 : 13:49:54

I would like to count the number of consecutive numbers in the column ITEMCODE for consecutive numbers, like 1 2 3 4 5 6 7 ext... if there are 5 - 11 consecutive numbers i would like to make a column with groups of consecutive numbers that have a count any where from 5 - 11
and that column contants 3 subcolumns that are the start end and count of the consecutive numbers. EX 1 2 3 4 5 6 7 would fall under the count 5 - 11 column and fuill in the three subcolumns as: start - 1; End - 7; range 7;

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 14:10:30

This is the classic islands and gaps problem, which can be solved a few different ways. One of the more efficient solutions is shown below.

The output of this query is not in the format that you specified. If you are going to be using the results in another query, or store the results in a database table, the format that this query produces may be better. On the other hand, if the results are to be presented to a user, the grouping that you describe may be better done in the presentation layer.


;WITH cte AS
(
   SELECT 
      ITEMCODE, 
      ITEMCODE - ROW_NUMBER() OVER(ORDER BY ITEMCODE) AS grp
   FROM #tmp
)
SELECT 
   MIN(ITEMCODE) AS startRange,
   MAX(ITEMCODE) AS endRange,
   CASE 
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE) > 22 THEN '> 22' 
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE) BETWEEN 18 AND 22 THEN '18-22'
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE) BETWEEN 12 AND 17 THEN '12-17'
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE) BETWEEN  5 AND 11 THEN ' 5-11'
      ELSE '< 5'
   END AS [Group]
   
FROM
   cte
GROUP BY
   grp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 14:13:45

Sorry forgot to copy the creation of the test data that I used.


CREATE TABLE #tmp (ITEMCODE INT);
insert into #tmp values (12);
insert into #tmp values (13);
insert into #tmp values (20);
insert into #tmp values (21);
insert into #tmp values (22);
insert into #tmp values (14);
insert into #tmp values (1);
insert into #tmp values (2);
insert into #tmp values (3);
insert into #tmp values (4);
insert into #tmp values (11);
insert into #tmp values (15);
insert into #tmp values (16);
insert into #tmp values (17);
insert into #tmp values (18);
insert into #tmp values (23);
insert into #tmp values (24);
insert into #tmp values (6);
insert into #tmp values (7);
insert into #tmp values (8);
insert into #tmp values (9);
insert into #tmp values (10);

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-13 : 14:34:27

you will still have unpredicable results in any case

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-13 : 14:59:40

Thank you so much for your help! If possible could you also add the column that says the exact count not just the range it falls in but both
Ex. 5, 5-11
8, 5-11
15, 12-17

That would be such a big help! Thank you!

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 15:13:19

You can add it like this:


.....
   END AS [Group],
   MAX(ITEMCODE)-MIN(ITEMCODE)+1 as ITEMCount
FROM
.....

Charles AWI
Starting Member

15 Posts

Posted - 2011-05-13 : 15:46:58

I got everything to work right i think lol i replaced [GROUP] with grp, idk if thats right, however, i did it becuase [GROUP] was gaving me an error...

I have one more thing if you wouldn't mind helping me with

SELECT ROWNUM FROM OMDATA.ORDERLINEITEM WHERE ROWNUM < 100000
MINUS
SELECT ITEMCODE FROM AWI.ITEMMASTER;
END

How would I set the result of this to a name like "RESULT" or "TESTTEST" so i could use it later in the code

WebFOCUS 7.6

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 18:32:48

Not quite clear to me the result you are looking for, but if it is to pick all ROWNUM from OMDATA.ORDERLINEITEM such that:


   a) ROWNUM < 100000 and,
   b) ROWNUM is not in AWI.ITEMMASTER

then, it could be done like this:


select
   ROWNUM
FROM 
   OMDATA.ORDERLINEITEM oo
WHERE
   ROWNUM < 100000
   and not exists
   (
       select * from AWI.ITEMMASTER ai
       where ai.ITEMCODE = oo.ROWNUM
   )

Regarding the issue with column name "Group": It is a reserved key word in SQL and probably in reporting services etc. So if you want to use that as a column name or alias, you will need to escape it, for example with square brackets like this: [Group]. Probably a better thing to do is to not use reserved words as column names, which is what you did.

BTW, in the query that I had posted earlier, the grouping may have been off by one, so please change it to:


   CASE 
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 > 22 THEN '> 22' 
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN 18 AND 22 THEN '18-22'
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN 12 AND 17 THEN '12-17'
      WHEN MAX(ITEMCODE)-MIN(ITEMCODE)+1 BETWEEN  5 AND 11 THEN ' 5-11'
      ELSE '< 5'
   END AS [Group]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources