Logo

Advertisement

Count The Number Of Rows In A UNION ALL Statement

bytes.com

Hi, Should be quite simple but can someone please tell me the best way to count the number of rows in an UNION ALL statement. I tried using @@ROWCOUNT but that doesn't seem to contain the correct number. Also, I assume that running the query again but just returning count(*) instead of the data is horribly inefficient (plus the code is then bloated.)? Thanks, Mark

View Answers
Very similar questions

Advertisement

Advertisement

Number Table Function www.sqlteam.com

This script is for an in-line table function, F_TABLE_NUMBER_RANGE, that generates a number table. The input parameters are the @START_NUMBER and @END_NUMBER. It returns a sorted result set containing all intergers from @START_NUMBER to @END_NUMBER inclusive. This is an improved version of a script that I posted on a topic a few weeks ago. I modified it to cross join fewer tables based on powers of 16, instead of powers of 2, because I found that this compiled and ran much faster for small result sets (less than 10,000 rows). This is the link to the other post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46252&whichpage=5 SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[F_TABLE_NUMBER_RANGE] GO create function dbo.F_TABLE_NUMBER_RANGE ( @START_NUMBERint, @END_NUMBERint ) /* This function returns an integer table containing all integers in the range of@START_NUMBER through @END_NUMBER, inclusive. The maximum number of rows that this function can return is 16777216. */ returns table as return ( selecttop 100 percent NUMBER = (a.NUMBER+b.NUMBER)+ -- Add the starting number for the final result set -- The case is needed, because the start and end -- numbers can be passed in any order case when @START_NUMBER <= @END_NUMBER then @START_NUMBER else @END_NUMBER end from ( Selecttop 100 percent NUMBER = convert(int,N01+N02+N03) From -- Cross rows from 3 tables based on powers of 16 -- Maximum number of rows from cross join is 4096, 0 to 4095 ( select N01 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 ) n01 cross join ( select N02 = 0 union all select 16 union all select 32 union all select 48 union all select 64 union all select 80 union all select 96 union all select 112 union all select 128 union all select 144 union all select 160 union all select 176 union all select 192 union all select 208 union all select 224 union all select 240 ) n02 cross join ( select N03 = 0 union all select 256 union all select 512 union all select 768 union all select 1024 union all select 1280 union all select 1536 union all select 1792 union all select 2048 union all select 2304 union all select 2560 union all select 2816 union all select 3072 union all select 3328 union all select 3584 union all select 3840 ) n03 where -- Minimize the number of rows crossed by selecting only rows -- with a value less the the square root of rows needed. N01+N02+N03 < -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) order by 1 ) a cross join ( Selecttop 100 percent NUMBER = convert(int, (N01+N02+N03) * -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) ) From -- Cross rows from 3 tables based on powers of 16 -- Maximum number of rows from cross join is 4096, 0 to 4095 ( select N01 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 ) n01 cross join ( select N02 = 0 union all select 16 union all select 32 union all select 48 union all select 64 union all select 80 union all select 96 union all select 112 union all select 128 union all select 144 union all select 160 union all select 176 union all select 192 union all select 208 union all select 224 union all select 240 ) n02 cross join ( select N03 = 0 union all select 256 union all select 512 union all select 768 union all select 1024 union all select 1280 union all select 1536 union all select 1792 union all select 2048 union all select 2304 union all select 2560 union all select 2816 union all select 3072 union all select 3328 union all select 3584 union all select 3840 ) n03 where -- Minimize the number of rows crossed by selecting only rows -- with a value less the the square root of rows needed. N01+N02+N03 < -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) order by 1 ) b where a.NUMBER+b.NUMBER < -- Total number of rows abs(@START_NUMBER-@END_NUMBER)+1and -- Check that the number of rows to be returned -- is less than or equal to the maximum of 16777216 case when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216 then 1 else 0 end = 1 order by 1 ) GO GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public] GO -- Demo using the function to ruturn numbers 1 to 2000 select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000) -- Demo using the function to ruturn numbers -1500 to 2000 select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000) CODO ERGO SUM

Loop && Count www.vbcity.com

Code: Public Sub Criteria()    ActiveWorkbook.Sheets(1).Select    Dim col As Integer    col = 1 ' this can be passed into the sub    Dim cel As Range    Dim SelRange As Range    Dim SelRange2 As Range    Dim SelRange3 As Range    Dim SelRange4 As Range    Dim SelRange5 As Range    Dim SelRange6 As Range    Dim SelRange7 As Range    Dim SelRange8 As Range    Dim SelRange9 As Range    Dim SelRange10 As Range    Dim SelRange11 As Range    Dim SelRange12 As Range    Dim SelRange13 As Range    Dim SelRange14 As Range    Dim SelRange15 As Range    Dim SelRange16 As Range    Dim SelRange17 As Range    Dim SelRange18 As Range        For Each cel In Range(Cells(2, col), Cells(65536, col).End(xlUp))        Select Case cel.Value            Case "2FBARS1"                If SelRange Is Nothing Then                    Set SelRange = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                    BacklogFinder.lbl2fbars1total.Caption = "0"                Else                    Set SelRange = Union(SelRange, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                    BacklogFinder.lbl2fbars1total.Caption = SelRange.Count / 52                End If                        Select Case cel.Offset(0, 10).Value ' offset 10 columns to right                             Case 0 To 4                                If SelRange2 Is Nothing Then                                    Set SelRange2 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars104.Caption = "0"                                Else                                    Set SelRange2 = Union(SelRange2, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars104.Caption = SelRange2.Count / 52                                                    End If                             Case 5 To 12                                If SelRange3 Is Nothing Then                                    Set SelRange3 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars1512.Caption = "0"                                Else                                    Set SelRange3 = Union(SelRange3, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars1512.Caption = SelRange3.Count / 52                                                             End If                             Case 13 To 23                                If SelRange4 Is Nothing Then                                    Set SelRange4 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars11323.Caption = "0"                                Else                                    Set SelRange4 = Union(SelRange4, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars11323.Caption = SelRange4.Count / 52                                                                    End If                             Case 24 To 91                                If SelRange5 Is Nothing Then                                    Set SelRange5 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars12491.Caption = "0"                                Else                                    Set SelRange5 = Union(SelRange5, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars12491.Caption = SelRange5.Count / 52                                        End If                             Case 92 To 1000                                If SelRange6 Is Nothing Then                                    Set SelRange6 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars191up.Caption = "0"                                Else                                    Set SelRange6 = Union(SelRange6, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars191up.Caption = SelRange6.Count / 52                                                                    End If                        End Select            Case "2FBARS2"                If SelRange7 Is Nothing Then                    Set SelRange7 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                    BacklogFinder.lbl2fbars2total.Caption = "0"                Else                    Set SelRange7 = Union(SelRange7, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                    BacklogFinder.lbl2fbars2total.Caption = SelRange7.Count / 52                End If                        Select Case cel.Offset(0, 10).Value ' offset 10 columns to right                             Case 0 To 4                                If SelRange8 Is Nothing Then                                    Set SelRange8 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars204.Caption = "0"                                Else                                    Set SelRange8 = Union(SelRange8, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars204.Caption = SelRange8.Count / 52                                                    End If                             Case 5 To 12                                If SelRange9 Is Nothing Then                                    Set SelRange9 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars2512.Caption = "0"                                Else                                    Set SelRange9 = Union(SelRange9, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars2512.Caption = SelRange9.Count / 52                                                             End If                             Case 13 To 23                                If SelRange10 Is Nothing Then                                    Set SelRange10 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars21323.Caption = "0"                                Else                                    Set SelRange10 = Union(SelRange10, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars21323.Caption = SelRange10.Count / 52                                                                    End If                             Case 24 To 91                                If SelRange11 Is Nothing Then                                    Set SelRange11 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars22491.Caption = "0"                                Else                                    Set SelRange11 = Union(SelRange11, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars22491.Caption = SelRange11.Count / 52                                                                    End If                             Case 92 To 1000                                If SelRange12 Is Nothing Then                                    Set SelRange12 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars291up.Caption = "0"                                Else                                    Set SelRange12 = Union(SelRange12, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars291up.Caption = SelRange12.Count / 52                                                                    End If                        End Select            Case "2FBARS3"                If SelRange13 Is Nothing Then                    Set SelRange13 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                    BacklogFinder.lbl2fbars3total.Caption = "0"                Else                    Set SelRange13 = Union(SelRange13, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                    BacklogFinder.lbl2fbars3total.Caption = SelRange13.Count / 52                End If                        Select Case cel.Offset(0, 10).Value ' offset 10 columns to right                             Case 0 To 4                                If SelRange14 Is Nothing Then                                    Set SelRange14 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars304.Caption = "0"                                Else                                    Set SelRange14 = Union(SelRange14, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars304.Caption = SelRange14.Count / 52                                                    End If                             Case 5 To 12                                If SelRange15 Is Nothing Then                                    Set SelRange15 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars3512.Caption = "0"                                Else                                    Set SelRange15 = Union(SelRange15, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars3512.Caption = SelRange15.Count / 52                                                             End If                             Case 13 To 23                                If SelRange16 Is Nothing Then                                    Set SelRange16 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars31323.Caption = "0"                                Else                                    Set SelRange16 = Union(SelRange16, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars31323.Caption = SelRange16.Count / 52                                                                    End If                             Case 24 To 90                                If SelRange17 Is Nothing Then                                    Set SelRange17 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars32491.Caption = "0"                                Else                                    Set SelRange17 = Union(SelRange17, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars32491.Caption = SelRange17.Count / 52                                                                    End If                             Case 91 To 1000                                If SelRange18 Is Nothing Then                                    Set SelRange18 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                    BacklogFinder.lblbars391up.Caption = "0"                                Else                                    Set SelRange18 = Union(SelRange18, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                    BacklogFinder.lblbars391up.Caption = SelRange18.Count / 52                                                                    End If                        End Select        End Select    NextEnd SubThis is a sample of the code im using.It searches the spreadsheet rows and finds the matches then creates a count value on a label. The count is always divided by 52 because a union happens which looks at 52 columns.So 1 match is actually 52 cells when it should be 1. this works fine.Anyways the code works perfect except if only one result is returned.Then the code activates the selrange to be marked as 0.Can anyone think of a simple fix for this.The problem is this is a 3 criteria sample the real version has about 50 select cases.thanksAl http://www.stormitsolutions.comhttp://www.stormitsolutions.co.ukAllan Nisbet - Web & Software DeveloperVB *.*, PHP, D/HTML, VBScipt, ASP, CSS, MySQL, Access, MSOffice, ***Please Mark Posts Resolved when you get your answer***SQL is the Devil!Edited by - Big Al Inc on 9/6/2007 3:12:34 AM

Get Count From Union Select bytes.com

Hi, I'm trying to get the count of rows from the union of several tables. My code is: select count(*) from (select * from #AdvSearch_Main union select * from #AdvSearch_Atty) This will not get past the syntax check saying that the error occurs on the final closing ")". Can someone tell me how to correctly write this? Thanks, Glen -------------------------- Numbers 6:24-26 -------------------------- -------------------------- Numbers 6:24-26 -------------------------- *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!

Query Re-writes Itself And Throws Error www.access-programmers.co.uk

Hi, I have this query here: SELECT sum(subtotal) AS final_count FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) from 1_30_06 union all select count(*) from 2_6_06 WHERE Role='EUM' union all select count(*) from 2_13_06 WHERE Role='EUM' union all select count(*) from 2_20_06 WHERE Role='EUM' union all select count(*) from 2_27_06 WHERE Role='EUM']. AS all_counts; I save it as a query then I try and run it, but I get an error saying that the path is invalide. When I open the query to look at the SQL it looks like this: SELECT sum(subtotal) AS final_count FROM [select count(*) as subtotal from 1_2_06 WHERE Role='EUM' union all select count(*) from 1_9_06 WHERE Role='EUM' union all select count(*) from 1_16_06 WHERE Role='EUM' union all select count(*) from 1_23_06 WHERE Role='EUM' union all select count(*) fro] AS all_counts; Why is this happening?

UNION In A WHILE Statement?? forums.asp.net

 Hello, Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT SET @counter = 0 WHILE @counter < 24 BEGIN SELECT "Hour"=(SELECT @counter), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) SET @counter = @counter + 1 ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION SELECT "Hour"=(SELECT 1), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart   

Union www.webmasterworld.com

The Mysql UNION code works directly with the mysql.exe -direct with db-- but when put in a PHP code, errors out. $resp = mysql_query("(SELECT account, 'GT' as status, count(ID) as total FROM tblx GROUP BY account HAVING count(ID) > 10) UNION ("(SELECT account, 'LT' as status, count(ID) as total FROM tblx GROUP BY account HAVING count(ID) < 10)"); while ($row = mysql_fetch_array($resp)) { print ("the status is: " . $row{'status'}; } Any insight?

Count From Two Tables www.phpfreaks.com

I got a problem when I do a count from two tables and when I have an UNION between them. MY select looks like this: SELECT DISTINCT COUNT(ID) AS n FROM Table1 WHERE age = 10 UNION SELECT COUNT(ID) AS n FROM Table2 WHERE age = 10 This give me a table with two rows. But what I want is one row which has counted this two tables and sorted out all dubletts so I only get the DISTINCT values.

Problem With Union Inside A While Loop social.msdn.microsoft.com

� im trying to do this � � declare @count int set @count=0 while @count<4 begin set @count=@count+1 select * from dbo.Categories where CategoryPID=-1 union() end � � and i get a error this is not the original code but i want to union all select statements please help !!!

Get Table Names. www.sqlteam.com

I like to print the table names that starts with 'table'. I'm getting the same table name four times. Instead of four different table name. create table table1(tableID int, tableName varchar (30)) insert table1 select 1, 'tableSam' union all select 2, 'tableDaniel' union all select 3, 'tableRon' union all select 4, 'tableKen' union all select 5, 'tableHonda' create table table2(tableID int, tableName varchar (30)) insert table2 select 6, 'tableSam' union all select 7, 'tableDaniel' union all select 8, 'tableRon' union all select 9, 'tableKen' union all select 10, 'tableHonda' create table table3(tableID int, tableName varchar (30)) insert table3 select 11, 'tableSam' union all select 12, 'tableDaniel' union all select 13, 'tableRon' union all select 14, 'tableKen' union all select 15, 'tableHonda' create table table4 (tableID int, tableName varchar (30)) insert table4 select 16, 'tableSam' union all select 17, 'tableDaniel' union all select 18, 'tableRon' union all select 19, 'tableKen' union all select 10, 'tableHonda' declare @string varchar(330) declare @tableName varchar(30) declare @count int set @count = 0 while (@count < 5) begin select @tableName = table_name from information_schema.tables where table_name like 'table%' exec(@string) print @tablename set @count = @count + 1 end

Find Values In Multiple Columns. www.vbcity.com

Hi guys.My client has double backed on what they want and i'm starting this project again.I'm trying to loop thru a range and count the matches.Example.Loop Column A and count all values which have "A" as valueSet the count value as a label1.captionLoop Column B for value of "1" and Column A value of "A"set the count value as label2.captionLoop Column C for value of "1","4" or "2","8","9" or "3","5" and column A value of "A" and column B value of "1"set the count value as label3.captionthis is then repeated for Column A value of somthing else but the Column B and C will remain the same.I've tried with If statements and failed, im now trying with select cases.Code: Public Sub Basic()    ActiveWorkbook.Sheets(1).Select    Dim col As Integer    Dim cel As Range    Dim SelRange As Range    Dim SelRange2 As Range    Dim SelRange3 As Range        Dim totalrows As Integer        For col = 1 To 1 'columns A to A        For Each cel In Range(Cells(1, col), Cells(65536, col).End(xlUp))            Select Case cel                Case 1                    Select Case cel                        Case 1                            If SelRange Is Nothing Then                                Set SelRange = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                totalrows = SelRange.Count                                BacklogFinder.lblfreqtotal1.Caption = totalrows                            Else                                Set SelRange = Union(SelRange, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                totalrows = SelRange.Count                                BacklogFinder.lblfreqtotal1.Caption = totalrows / 52                            End If                    End Select                Case "2"                    Select Case cel                        Case "2FBARS2"                            If SelRange2 Is Nothing Then                                Set SelRange2 = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                totalrows = SelRange2.Count                                BacklogFinder.lblfreqtotal2.Caption = totalrows                            Else                                Set SelRange2 = Union(SelRange2, Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                totalrows = SelRange2.Count                                BacklogFinder.lblfreqtotal2.Caption = totalrows / 52                            End If                    End Select                End Select        Next cel        Next colend subnotice the / 52 after the totalrows.This is because is makes a union of all 52 columns which is needed for the future part.The future part is to copy all the matching rows and paste them into a spreadsheet.I divide by 52 as if it finds a match for cell value the union binds teh cells which total 52 and make a boo boo of the overall count.ANy ideas would be great.Al http://www.stormitsolutions.comhttp://www.stormitsolutions.co.ukAllan Nisbet - Web & Software DeveloperVB *.*, PHP, D/HTML, VBScipt, ASP, CSS, MySQL, Access, MSOffice, ***Please Mark Posts Resolved when you get your answer***SQL is the Devil!Edited by - Big Al Inc on 8/30/2007 7:23:23 AM

Advertisement

If Else Operators phpbuilder.com

I am attempting to run an if statement that runs if the lowest number is equal to or less than a number and the highest runs if it is equal to or less than a number. Here is what I have tried so far. //Query that is being run $count = mysql_query("SELECT * FROM Times"); $row = mysql_num_rows($count); ==== I have tried the following====== if ($row >= 1 || $row => 8) if ($row >= 1) || ($row => 8) if (($row >= 1) || ($row => 8)).

DataSource Controls :: Return Number Of Rows Affected For Multiple Queries In One SQLCommand? forums.asp.net

I am accepting query/queries from user (our support team) in a text box where user can enter only one query or multiple queries. I need to display result of all queries entered in the textbox. If it is SELECT statement then result of that statement in grid which is done. If UPDATE/INSERT/DELETE then total number of rows affected which is also done but if only one UPDATE/INSERT/DELETE statement is entered in the textbox. If user enters 2 UPDATE statements and then a SELECT statement then how can I get number of rows affected for individual UPDATE statements just like SQL Server Qury Analyzer displays messages in its result pane. e.g. (6 row(s) affected) -- first UPDATE statement (4 row(s) affected) -- second UPDATE statement (16 row(s) affected) -- for SELECT statement (grid will also be displayed along with these messages). I tried almost everything, SqlDataSource (returns count of first statement only), SqlCommand.ExecuteNonQuery and DataAdapter.Fill (returns count of last statement only).

Can Some One Help Me Write It Shorter? :chomp: forums.devshed.com

I need query like this that make a row of numbers (in one execute) SELECT a from ( SELECT -1 a UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL SELECT -5 UNION ALL SELECT -6 UNION ALL SELECT -7 UNION ALL SELECT -8 UNION ALL SELECT -9) t;

Grouping / Reporting Question www.sqlteam.com

Hi, I've written a series of procedures that are writing output lines into a common table that I'm then reporting on. I've included some simplified code to show waht I've done. I was hoping someone could tell me if it is possible to remove the loop that I've created to get the report headings. I've tried to write this as a with statement, but can't quite get it right. -------- CREATE TABLE JobReport( JobRunId int NOT NULL, ReportDate datetime NOT NULL, Process] varchar(200) NOT NULL, Line varchar(200) NOT NULL ) INSERT INTO jobreport(jobrunid,reportdate,process,line) SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A1' union SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A2' union SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A3' union SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b1' union SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b2' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b3' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c1' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c2' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c3' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c4' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d1' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d2' UNION SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d3' SELECT IDENTITY(INT,1,1) id,headings.Process INTO #headings from ( SELECT Process FROM jobreport WHERE jobrunid = 1 GROUP BY Process ) headings BEGIN DECLARE @max INT, @COUNT INT,@heading AS VARCHAR(200),@report VARCHAR(MAX), @jobrunid INT SET @jobrunid = 1 set @report = '' SELECT @max = COUNT(*) FROM #headings SET @count = 0 WHILE @count < @max BEGIN SET @count = @count + 1 SELECT @heading = h.Process FROM #headings h WHERE h.id = @count SELECT @report = @report + '----' + @heading + '----' + CHAR(13) SELECT @report = @report + line + CHAR(13) FROM jobcontrol.dbo.jobreport rep WHERE rep.jobrunid = @jobrunid AND rep.Process = @heading END -------- any help would be appreciated. thanks Sean

Faster Way To Do This? social.msdn.microsoft.com

I want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds). Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate. USE [TNS] GO /****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[Unique_Login_IPs] ( @year1 int, @year2 int ) AS BEGIN SET NOCOUNT OFF; -- Define the years for testing purposes set @year1 = 2006 set @year2 = 2007 SELECT month,[2006] as y2006,[2007] as y2007 FROM ( SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1 UNION SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2 UNION SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3 UNION SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4 UNION SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5 UNION SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6 UNION SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7 UNION SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8 UNION SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9 UNION SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10 UNION SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11 UNION SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12 UNION SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1 UNION SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2 UNION SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3 UNION SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4 UNION SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5 UNION SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6 UNION SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7 UNION SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8 UNION SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9 UNION SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10 UNION SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11 UNION SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt FROM (SELECT DISTINCT ipaddress FROM servicelog AS servicelog_1 WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12 ) piv PIVOT ( SUM(cnt) FOR year IN ([2006],[2007]) ) as child END

Count Fields In Row? stackoverflow.com

How can I count how many fields there are in this row: $row['number'] while ($row = mysql_fetch_array($result)) { echo ' <td class="alt">'.$row['number'].'</td> $number = $row['number'] }

Example Code For User Tree www.dbforums.com

One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks GO DROP TABLE LI_Users GO DECLARE @d1DATETIME , @d2DATETIME , @d3DATETIME , @d4DATETIME , @d5DATETIME , @d6DATETIME , @d7DATETIME , @d8DATETIME SELECT @d1 = GetDate() CREATE TABLE LI_Users ( uidINT PRIMARY KEY (uid) ) SELECT @d2 = GetDate() CREATE TABLE LI_UserLinks ( uid_fromINT CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from) REFERENCES LI_Users (uid) , uid_toINT CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to) REFERENCES LI_Users (uid) CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to) ) ALTER TABLE LI_Userlinks ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to) SELECT @d3 = GetDate() INSERT INTO LI_Users ( uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5 FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z2 CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z3 CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z4 CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z5 SELECT @d4 = GetDate() INSERT INTO LI_UserLinks ( uid_from, uid_to) SELECT uid, 100 * uid + n0 + 10 * n1 FROM LI_Users CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 WHERE LI_Users.uid BETWEEN 0 AND 99 AND uid != 100 * uid + n0 + 10 * n1 SELECT @d5 = GetDate() INSERT INTO LI_UserLinks ( uid_from, uid_to) SELECT uid, 100 * uid + n0 + 10 * n1 FROM LI_Users CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 WHERE LI_Users.uid BETWEEN 100 AND 9999 SELECT @d6 = GetDate() SELECT u.uid, r1.uid_to, r2.uid_to FROM LI_Users AS u INNER JOIN LI_UserLinks AS r1 ON (r1.uid_from = u.uid) INNER JOIN LI_UserLinks AS r2 ON (r2.uid_from = r1.uid_to) WHERE 1 = u.uid SELECT @d7 = GetDate() SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to) FROM LI_Users AS u INNER JOIN LI_UserLinks AS r1 ON (r1.uid_from = u.uid) INNER JOIN LI_UserLinks AS r2 ON (r2.uid_from = r1.uid_to) SELECT @d8 = GetDate() SELECT DateDiff(ms, @d1, @d2) , DateDiff(ms, @d2, @d3) , DateDiff(ms, @d3, @d4) , DateDiff(ms, @d4, @d5) , DateDiff(ms, @d5, @d6) , DateDiff(ms, @d6, @d7) , DateDiff(ms, @d7, @d8) , DateDiff(ms, @d1, @d8)-PatP

VB6 - Formating Excel Spreadsheets - Multiple Columns With Count, Sum And Totals. visualbasic.ittoolbox.com

Hi everyone,can anyone assist me in format an excel spreadsheet. This spreadsheet has 4 columns: "Population", "Check Count", "Total Amount" and "Union Code".My objective is to Count "Check Amount", Count "Union Code", Sum/Total "Total Amount" and GroupBy "Population".This is the code I'm using,but it's not quite doing it.'This module does the count of checks in the spreadsheet (by Union Code).'

Stored Procedure Help www.xtremevbtalk.com

I'm having trouble with this stored procedure. I'm trying to get a cumulative count on several queries using the UNION statement. It works great until I slide in the @outparam... then it errors on me:"ADO error: Only the first query in a UNION statement can have a SELECT with an assignment"It seems as if it is a violation include @outparam in more than one select statement? Or perhaps I'm going about this wrong?Code:Alter Procedure sp_BIO_Count(@outparm int OUTPUT,)ASSELECT @outparam=COUNT(*) From blah blah blah...UNIONSELECT @outparam=COUNT(*) From blah blah blahUNIONSELECT @outparam=COUNT(*)From blah blah blahAny advice???diver

Counting Problem forums.asp.net

Hi everyone, another problem: I'm trying to count the number of rows but it's not working. Here's my code: SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites] FROM [Activities] WHERE [Person ID] IN ( SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people) FROM [Activites] ) As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out. Any help? Andrew

Table Variable (bug)? www.sqlteam.com

I ran into this while testing some stuff and was wonding if it is some known behavior or not? All that I'm doing is create a table variable, loading it with some data and then updating a random number of rows to a value. In this example I'm trying to update 3 rows and setting the Code columm to 'A'. You will notice that there are 3 declarations of the table variable at the top of the script. I get why the first one may not work, but I would expect the second one to wokr correctly, but only the thirsd one works correctly. By "correctly" I mean updates 3 and only 3 rows. (EDIT: I should probably note that I just hit "refresh" a bunch of times to notice that the first 2 table declarations updated a variable number of rows, for waht it's worth). Maybe I can;t see the forest for the tree or somethig, but here is the script:DECLARE @Foo TABLE (ID INT IDENTITY, Bar INT, Code CHAR(1)) --DECLARE @Foo TABLE (ID INT IDENTITY(1, 1), Bar INT, Code CHAR(1)) --DECLARE @Foo TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY, Bar INT, Code CHAR(1)) DECLARE @Count INT INSERT @Foo SELECT 1, NULL UNION ALL SELECT 2, NULL UNION ALL SELECT 3, NULL UNION ALL SELECT 4, NULL UNION ALL SELECT 5, NULL UNION ALL SELECT 6, NULL UNION ALL SELECT 7, NULL SET @Count = 3 SELECT * FROM @Foo UPDATE Foo SET Code = 'A' FROM @Foo AS Foo INNER JOIN ( SELECT ID, ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNum FROM @Foo ) AS Temp ON Foo.ID = Temp.ID WHERE Temp.RowNum <= @Count SELECT * FROM @Foo

MySQL 4.1.19 (UNION Question) phpbuilder.com

I wasnt sure if MySQL version 5 has changed this, so I stated my version in the topic. Im trying to pull a count set from 2 tables and return it with a union like so; $sql = $db->query('SELECT count(*) as total_users ����FROM c_users ����UNION ����SELECT count(*) as total_online ����FROM c_active'); ���� ����while($row = $sql->fetch()) { �������� ��������print_r($row); �������� ����} Now, as you can see I've attempted to label the first result set total_users and the 2nd result set total_online. This does not work, as both sets are being named total_users. Any advice? I've read the MySQL Manual page on Unions and the closest thing I can find still wont label the rows as needed.

Linq To DataTable Not Producing Distinct Values? stackoverflow.com

I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist Public Class Color Private _c_olor As String Public Property c_olor() As String [code].....

More Help In Set Based Sql ... www.sqlteam.com

Hi, I have a problem that I can solve in a procedural way but I would rather solve with set based and I am having a hard time wrapping my mind around it. First I will try an English description of problem: The tables involved are (data insert statements are at end of post) … The ProgramData table: create table #ProgramData (clt_num int, pgm_num int, beg_dte datetime) Following business rules listed below this table will be used to determine updates and inserts into the CafasData table: create table #cafasData (clt_num int, log_on varchar(10)) pgm_num are associeated with log_on values in a one to many relationship defined in this lookup table: create table #cafas_log_on_lookup (pgm_num int, log_on varchar(10)) There can be multiple rows for one client in the ProgramData table but only one row for clients in the CafasData table. As a client can be in two or more programs that match to different log_ons at the same time a weight has been assigned each log_on and the log_on with the greater weight is used in the CafasData Table. DDL for weight table: create table #CafasLogOnWeights (Log_on varchar(10), weight int) Programs have begin and end dates (end dates for this problem will always be in the future so I did not include them in the ddl). The basic rules for assigning a log_on to a client is: 1.Once in a log_on it will not change until after the program has ended. Regardless if there is another current log_on with more weight. 2.Always look at the minimum beg_dte that has an end date in the future (assume all test data does have an end date in the future). 3.If two or more programs start on the same date and none of them are for the current log_on then change the log_on to the one with the greatest weight (see #CafasLogOnWeights table below). Ok ... If that is completely confusing I will try some pseudo procedural code for explaining what needs to be done: For a given clt_num: Get all rows in the ProgramData table with a minimum beg_dte If one row then If clt_num in CafasData then Update CafasData with log_on = associated log_on for given pgm_num Else Create new row in CafasData with log_on = associated log_on for given pgm_num End if Else If clt_num in CafasData then If one of the pgm_nums translates to the current log_on value then Do nothing Else Pick log_on with 'heaviest' weight End if Else Create new row in CafasData with log_on with 'heaviest' weight End if End if End for Sample data for the four tables defined above: insert into #cafasData (clt_num, log_on) select 1, 'SB' union all select 2, 'Intake' union all select 3, 'HB' union all select 5, 'Wrap' union all select 6, 'SB' union all select 7, 'Intake' insert into #cafas_log_on_lookup (pgm_num, log_on) SELECT 1220, 'SB' UNION ALL SELECT 1222, 'Wrap' UNION ALL SELECT 1223, 'Wrap' UNION ALL SELECT 1228, 'HB' UNION ALL SELECT 1229, 'HB' UNION ALL SELECT 1233, 'Intake' UNION ALL SELECT 1234, 'Other' UNION ALL SELECT 1238, 'Other' UNION ALL SELECT 1239, 'Intake' insert into #CafasLogOnWeights (log_on, weight) SELECT 'HB', 5 UNION ALL SELECT 'Wrap', 4 UNION ALL SELECT 'SB', 3 UNION ALL SELECT 'Intake', 2 UNION ALL SELECT 'Other', 1 insert into #ProgramData (clt_num,pgm_num,beg_dte) Select 1, 1222, '11/30/2006' union all select 1, 1220, '11/30/2006' union all select 2, 1222, '11/30/2006' union all select 2, 1220, '11/30/2006' union all select 3, 1222, '11/30/2006' union all select 3, 1220, '11/30/2006' union all select 4, 1222, '11/30/2006' union all select 4, 1220, '11/30/2006' union all select 5, 1222, '11/30/2006' union all select 5, 1220, '11/30/2006' union all select 6, 1234, '10/30/2006' union all select 6, 1222, '11/30/2006' union all select 6, 1220, '11/30/2006' union all select 7, 1234, '10/30/2006' union all select 7, 1222, '11/30/2006' union all select 7, 1220, '11/30/2006' union all select 8, 1234, '10/30/2006' union all select 8, 1222, '11/30/2006' union all select 8, 1220, '11/30/2006' union all select 9, 1223, '1/15/2008' union all select 10, 1222, '1/1/2007' union all select 10, 1223, '1/1/2007' Expected Output (if I didn't make any mistakes or typos): Inserted Rows Clt_numlog_on 4Wrap 8Intake 10Wrap Changed Rows Clt_numlog_on 2Wrap 3Wrap 5Wrap 6Intake No Change Clt_numlog_on 1SB 7Intake clt_num = 9 is a trick row as the begin date is in the future and there fore should have no affect on the CafasData table Please let me know if this does not make any sense and give me the opportunity to clarify any points as I really do need to solve this problem. Thanks in advance for any help Laurie P.S. I will be leaving work at 4:30 est. but will check back first thing in the morning. Please do not think I am ignoring any responses.

Count & Compare Used Rows On 2 Sheets www.ozgrid.com

I want to count the number of data rows in Sheet 1, Count the number of data rows in Sheet 2 and display a message if they are not equal. I have tried various IF(COUNTA statements but cannot get the correct syntax. MACKE

Cross Tab Char/varchar (a Solution) www.sqlteam.com

Louis writes "Thought I share this snippet. You can "concatenate" over rows, using the update statement. The result rows are: ROW1 [C,], ROW2 [B,C], ROW3 [A,B,C]. create table #me (text varchar(1),notes varchar(100)) insert into #me select text='A',notes=' ' UNION select text='B',notes=' ' UNION select text='C',notes=' ' declare @text varchar(1000) select @text = null update #me set @text = notes = text +','+ isnull(@text,'') select * from #me"

Count The Number Of Occurences Of Pages www.webmasterworld.com

i have two tables both holding similar info(trust me its been normalized as far as pos) anyway i query table1 and get results with headings: id, page i then query table2 and get a table with id and page. how can i add the rows together (union removed suplicates). I want to do this so i can count the number of occurences of pages. I can do this easy its just the adding of the rows together?

.net - Linq To SQL With Multiple Unions? stackoverflow.com

I have a linq union statement that has been giving me some trouble and I can't see where the issue is.The error is....All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. I know the error means that I am selecting different amounts of elements in one of the linq statements, but I've examined this query extensively and I haven't been able to see that as the issue. [Code]...

Count In Query www.access-programmers.co.uk

how can i count the number of rows in a query. i have a query that return me number of rows. i want to count the rows. when i try to count it, it gave me a column that count only her column. which mean in every row it counts me '1'. i want to count all the rows.

Count The Names In A Column Based On Another Column www.excelforum.com

I'm using this formula to count the number of times this statement is in Column "D"

VB6 - Do XlCount Of Multiple Columns In A Spreadsheet Within Vb www.vbforums.com

Please see spreadsheet attachment.I need assistance/clarification on manipulating excel spreadsheet.I have this spreadsheet with 4 columns:"Population" "Check County" "Total Amount" "Union Code".My objective is first to GroupBy/Count "Population", Count "Check Count", Provide a Sum on "Total Amount" and finally do a Count on "Union Code".So far the code below is able to give me:count of Union code, count of Check Count, and Total of Total Amount.Code:'This module does the count of checks in the spreadsheet (by Union Code).'==================================================================With xlApp .Selection.Subtotal GroupBy:=4, TotalList:=Array(4), PageBreaks:=False, _ Function:=xlCount, Replace:=False, SummaryBelowData:=TrueEnd With'This module does the Subtotals and Grand Total of the Check Amounts:'====================================================================With xlApp .Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=TrueEnd WithAny assistance or insight will be appreciated.GiftX.

MySQL: Using Union To Check If Two Columns Contain Eachother's Values? stackoverflow.com

I have a friend table with two primary columns (uid and fid) and am essentially trying to see if for example if the friend is also friends of the user, so uid 5 -> fid 6 uid 6 -> fid 5 I had tried to mix and mash SELECT queries to somehow return a deciding result, however cannot seem to find anything. count() will return either one or two columns (two being correct), however PHP's PDO has no good means to find number of selected (not affected) rows without hackish loops. (SELECT count(uid) FROM friends WHERE uid = 1 AND fid = 2) UNION ALL -- Or plain union (SELECT count(uid) FROM friends WHERE uid = 2 AND fid = 1) This of course still returns one or two rows of 0/1 or 0 or 1 Can you think of a way to return either 1 or 0 in MySQL in the first column? or a better way of writing this functionality in to my program? (I try best to avoid hackish things)

Gridview.Rows.count In Multipage Gridview? stackoverflow.com

I have a GridView that the paging is true and I want to take the number of rows but only the number of rows to page 1 return, Gridview.Rows.count

Count The Number Of Days Exactly Current Date To Only One Month Back Not More Then One Month Back? stackoverflow.com

1. number of rows all 2. count the number of days exactly currentdate to only one month back not more then one month back select count(id) from table where campaign_edate(exactly 1monthback) < CURDATE( )` how to write query for this? ex id campaign_edate 1 29-03-2011 2 28-02-2011 3 1-03-2011 4 10-03-2011 result returns number of days 2 3. count number of rows where expirydate>currentdate I want query for the above three count(number of rows)..query want to return 3count as result

Sequence Number For Records social.msdn.microsoft.com

Hi... � I have Sql statement more like this � SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.*�FROM�EmployeeA a UNION SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a � � �������������������rec_num����� employeeID��� employeeName����� employeeDepartment ��� 1���������������� 777���������� Mike�������������������������� �� HR ��� 2���������������� 888���������� Susy����������������������������� HR ��� 1�����������������111���������� Smith��������������������������� TECH ��� 2�����������������222���������� John����������������������������� TECH ��� 3�����������������333���������� Lenny��������������������������� TECH�� � � How do i get sequence number for all of this records.�The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this : � �������������������rec_num����� employeeID��� employeeName����� employeeDepartment ��� 1���������������� 777���������� Mike�������������������������� �� HR ��� 2���������������� 888���������� Susy����������������������������� HR ����3�����������������111���������� Smith��������������������������� TECH ����4�����������������222���������� John����������������������������� TECH ����5�����������������333���������� Lenny��������������������������� TECH��

Combining Multiple Sources Into 1 Row social.msdn.microsoft.com

� OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component.� Each source returns 1 row of data with 1 column each except the Script Component.� It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry.� I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component?� Please if anyone can help that would be wonderful.

COUNTIF Returns 0 Display Text Instead? www.mrexcel.com

I have a question about using COUNTIFS. I have a countifs statement in one of my workbooks. When it does not count anything instead of it displaying 0, I would like it to read No Scans. =COUNTIFS(DATA!$C:$C**(this is an account number row)**,A5**(A5 is the account number)**,DATA!$K:$K,**(This is an event column and I want everything with the code I to count)**"I"**(this is the event letter I am seeking for it to count)** =COUNTIFS(DATA!$C:$C,A5,DATA!$K:$K,"I")

Number Of Table Rows Count In Total ? www.phpfreaks.com

Is there a way to count in total how many rows in a table?I made a PM system, and i want to make it so the admin can send a "system message" which is sent from userid number 7.However to do this it should send to all users except userid 7 in the table "members" on my database like so:Subject & Message are entered in a form.get total number of members count = 0 while count < number of members if userid(count) != 7 send message increase count and stop when complete.I can handle the actual coding im just wondering how to count the total number of rows for a table.

Getting Number Of Rows Returned By A Query? www.sitepoint.com

When I do a COUNT/GROUP BY in a query, then a mysql_num_rows(), they return the same number instead of the telescoped number and full number of rows. Right now I run the query twice, first for the COUNT/GROUP BY to get the grouped rows, then again to get the full row count by leaving off the COUNT/GROUP BY: PHP Code: $sqlqueryc�=�"SELECT�tier_level3_brand,�COUNT(*)�AS�Number FROM�cover_tiers WHERE�1� GROUP�BY�tier_level3_brand [Code].... Is there a more efficient way than running a query twice like this?

Counting The Number Of Rows In A Database? social.msdn.microsoft.com

I am using a tableadapter to connect my project to an MS database. I need a a function which counts the number of rows in the table for something which I am doing. I have sucessfully used the count function to count the number of rows for my other tables, but when I tried to reuse it for another table, it wouldn't work. I get an error... An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dllAdditional information: Input string was not in a correct format.Couldn't store <AH25> in ProductCode Column. Expected type is Int32. and this is the code which I used Dim record As Integer = TAProduct.GetData.Rows Do Until record = TAProduct.GetData().Count ' other codes loop It only seems to work if the first column is an integer datatype, which is the case for my other tables, but the table which I am currently dealing with using a string datatype. Is there another way to count the number of rows without using SQL statements or can I adapt my existing code in some way?

MySQL: Row Counts From Mulitple Tables In One Shot phpbuilder.com

I'm looking to get the row counts from multiple tables, and I can't figure out the best way to do this. I thought of maybe storing it in a view. Here's an example of what I have: DROP VIEW IF EXISTS `status`; CREATE ALGORITHM = TEMPTABLE VIEW `status` (diecount) AS (SELECT COUNT(d.name) FROM die d WHERE d.wafer_name = "02H1") UNION (SELECT COUNT(r.idreadsummary) FROM readsummary r INNER JOIN die d ON d.iddie = r.iddie WHERE d.wafer_name = "02H1"); This doesn't seem to work if I try to create another column to put the second select statement result in. I want just one row with different columns for the results from each select statement. What would the best way to do this? Again, it doesn't have to be a view, but I'll use it. Thanks!

More - Aggravation Over Aggregates www.sqlteam.com

I have data that looks like this create table #TestData (clt_num int, proc_cde varchar(10)) insert #TestData (clt_num,proc_cde) select 1000, 'H0017' union all select 2000, 'T1016' union all select 2000, 'H0036' union all select 2000, 'T0017' union all select 3000, '99999' union all select 3000, 'AAAAA' union all select 3000, 'H0039' select 4000, '99999' union all select 5000, 'H0017' union all select 5000, 'H0066' union all select 100, 'H0036;TT' union all select 200, 'T1016;XX' union all select 200, 'H0036' union all select 300, '99999;HH' union all select 300, 'AAAAA' union all select 400, '99999' union all select 500, 'H0017;15' union all select 500, 'H0036;XX' I want to select records for a given clt_num based on weights (precedence) given to the different proc_cde(s). The rules for selecting the rows are: If clt_num only has one row select that row Select row that has the highest proc_cde by precedence for given clt_num If clt_num has two or more rows but no proc_cde with an assigned precedence select both rows. Note that there is a twist with the proc_cds in that they can have garbage at the end in the data records and I don't care about the garbage H0036, H1036;XX will have the same weight. Therefore my expected results are: clt_numproc_cde 100H0036;TT 200T1016;XX 30099999;HH 300AAAAA 40099999 500H0036;XX 1000H0017 2000T0017 300099999 3000AAAAA 400099999 5000H0039 In attempting to solve this problem I created a temp table called ProcCde_Weights: create table #ProcCde_Weights (proc_cde varchar(10), weight int) insert #ProcCde_Weights (proc_cde,weight) select 'H0039', 10 union all select 'T1017', 20 union all select 'T1016', 30 union all select 'H0036', 40 union all select 'T2011', 50 union all select '90806', 60 union all select '90862', 70 union all select 'T1002', 80 union all select 'H2031', 90 union all select 'H2023', 100 And wrote this query: select t.clt_num, t.proc_cde, case when p.weight is null then 1000 else p.weight end as weight from #TestData t left join #ProcCde_Weights p on t.clt_num = t.clt_num and p.proc_cde = left(t.proc_cde,5) which gives me this: clt_numproc_cdeweight 1000H00171000 2000T101630 2000H003640 2000T00171000 3000999991000 3000AAAAA1000 4000999991000 5000H00171000 5000H00661000 100H0017;TT1000 200T101630 200H003640 30099999;HH1000 300AAAAA1000 400999991000 500H0017;151000 500H0036;XX40 … but now I need to modify the query with using the min function or max function (I think) to only select rows according to the rules I gave. … and once again I seem to have a mental block when it comes to composing queries with aggregate functions. Thanks in advance for any help. Laurie p.s. can anybody point me to any books or tutorials that can help me wrap my mind around aggregate functions.

Forms Data Controls :: Adding Multiple Header Rows To Gridview forums.asp.net

I'm having difficulty adding a repeating header row, every x rows, to a gridview. I'm able to add the header only once with the below code. For some reason it will only add it during the last iteration through the for/next statement. I tried manually adding three separate .addat statements to different rows and it only executed the last .addat statement. I couldn't find any information on why it's only working the one time and I hope someone has a fix or better solution. Code snippet (Note the IsWhole() function verifies that the rownumber divided into a whole number): Protected Sub Gridview1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles Gridview.DataBound Dim grid As GridView = TryCast(sender, GridView) If grid IsNot Nothing Then Dim row As New GridViewRow(0, -1, DataControlRowType.Header, DataControlRowState.Normal) Dim header As GridViewRow = POSTURE_RESULT.HeaderRow For i As Integer = 0 To POSTURE_RESULT.Columns.Count - 1 Dim TableCell As New TableHeaderCell() TableCell.Text = header.Cells(i).Text row.Cells.Add(TableCell) Next Dim t As Table = TryCast(grid.Controls(0), Table) If t IsNot Nothing Then For i As Integer = 0 To Gridview1.Rows.Count - 1 If IsWhole(i / 5) Then t.Rows.AddAt(i, row) End If Next End If End If End Sub

Sum Of Two Union Queries bytes.com

Hi I have the following union query that retrieves two counts. Can I sum them up within this query, like wrap this in a sum function somehow to get the total count? Or is there a better way to do this. Please help. Using SQL 2000. select count(user_id) from table1 UNION select count(user_id) from table2

Multiple Operations On A Common Table Expression social.msdn.microsoft.com

Hi, I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression. � What�is wrong�with my syntax? � ;With OrderedTable AS ( select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee ) delete from OrderedTable where RowId != 1 SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc

Counting Every Nth Occurrence (with & Without A Condition) www.excelforum.com

I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I�d want to count the negative occurences in B2, E2, H2, K2 etc. Also, I�d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.

How To Create A File With Header, Details & Footer www.sqlteam.com

Hi All, I need to create a query which gives me something like this HH20060831160342 DDasb IT 3000 FF20060831160709000000001 Where 'HH' is the header(followed by Date and time) and 'FF' is the footer (followed by Date, time and no of records)and 'DD' has some details (few fields) from database.I am using UNION to get this result but the problem is that if the count in the footer is 0 then query should not give any output.but If I am using the following query select 'HH'+convert(varchar,getDATE(),112)+replace(convert(varchar,getdate(),8),':','') as filename,'' as name,'' as dept,'' as sal union all select 'DD'+'',filename,dept,sal from emp where empno like '%1%' union all select 'FF'+convert(varchar,getDATE(),112)+replace(convert(varchar,getdate(),8),':','')+ REPLICATE(0, 9-len(COUNT(*)))+''+convert(VARchar(10),COUNT(*)) as filename,'' as name,'' as dept,'' as sal from emp where empno like '%1%' I am getting the result as HH20060831161226 FF20060831161226000000000 if the second select statement has no records Please help

GridControl Doesn't Display The Data It Contains? stackoverflow.com

I am using DevExpress GridControl to display information from my database through a stored procedure (ie. SELECT * FROM aTable). Unfortunately when I run the program it doesn't display any of the information, columns, etc. It displays an empty table. I know that there is information contained by the GridView though as I have print statements that return the row count. Specifically:Console.WriteLine(GridView2.RowCount) ' returns the number of rows that should be displayedConsole.WriteLine(myTable.Rows.Count) ' returns the number of rows that should be displayedWhat should I do so that the actual data within the table (DataTable) and the DataView is displayed to the user. I know the row count is correct as when I add/remove a record the row count correlates by incrementing/decrementing.

Multiple Queries forums.databasejournal.com

Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one. SELECT COUNT(*) AS [NUMBER OF SERVERS] FROM DBO.OS WHERE (OSTYPE LIKE '%SER%') The other statement is SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS] FROM DBO.OS WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%') If any one has any idea's please reply or email me directly. I would greatly appreciate it.

SMS Retrieve From Table (sms_out) A List Of Mobile Numbers www.daniweb.com

I'm having a problem for a few days with an sql statement. Idea: I'm trying to retrieve from table (sms_out) a list of mobile numbers. Each number with it's status of sent i.e Failed or Success. The output should be like this: Mobile No. ------------ Failed--------------Success--------------Total 0171112554 5 10 15 My effort until now was this: This retrieve the mobile number. Then in Query1, it take the mobile number and get the number of FAILED. Query2 will get the number of SUCCESS. $query="SELECT out_phone, count(status), count(*) AS total FROM muath_sms_out GROUP BY out_phone, status"; $row=mysql_fetch_array($result) $hp=$row["out_phone"]; $query1="SELECT count(out_phone) as Failed FROM muath_sms_out where out_phone=$hp status='Failed'"; $result1=mysql_query($query1); $row1=mysql_fetch_array($result1); $query2="SELECT count(out_phone) as Success FROM muath_sms_out where out_phone=$hp status='Success'"; $result2=mysql_query($query2); [Code].....

MSFlex Grid Row Count www.vbcity.com

Hi,Is there anyway of doing a row count with a msflex grid?? Apart from doing a SQL statement???thanks team

Union All Does Not Union All Rows social.msdn.microsoft.com

Hi all, I have a Union All transformation with 4 inputs and one output when I debug the package the sum of�the different inputs�rows does not match the row count in output. I don't understand, I've used the Union All transform many times and I've never seen this. Any idea why this could happen ? �

Linked Server SQL UNION ALL Problem social.msdn.microsoft.com

Hi- � I'm importing an Excel spreadsheet into SQL Server using the spreadsheet as a linked server.� I've written the following query to do so: � Use TESTDB go SELECT * INTO VTAble FROM LINKSER...[DIST1$] UNION ALL SELECT * FROM LINKSER...[DIST2$] UNION ALL SELECT * FROM LINKSER...[DIST3$] UNION ALL SELECT * FROM LINKSER...[DIST4$] UNION ALL SELECT * FROM LINKSER...[DIST5$] UNION ALL SELECT * FROM LINKSER...[DIST6$] UNION ALL SELECT * FROM LINKSER...[DIST7$] � .... and it continues for 28 tables. � The spreadsheet tables are suposed to be identical, however I receive this error: � Msg 205, Level 16, State 1, Line 2 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. �I'm guessing that in a couple places in the spreadsheet their is some variance in the data.� I've experimented with running the query without SELECT statements for certain tables and have identified that there is something wrong with those tables.� Is there a more systematic way I can locate the spreadsheet errors?

MIN/MAX Across Multiple Columns www.sqlteam.com

I have seen questions posted a number of times where someone wants to find the maximum or minimum value from a set of columns in a single row of a table. This script demonstrates two methods for finding the maximum value in a row across a set of columns in the row when any or all of the columns are allowed to be null or equal. Method 1 uses a UNION ALL sub query for all the columns with a MAX. It is much simpler to code and test, especially when you get much past 4 columns. Adding another column is as simple as adding one more SELECT to the subquery. Method 2 uses a CASE statement to determine the MAX. It is much more complex to code (and test), and gets exponentially harder to code as the number of columns goes up. I think 5 or 6 columns may be about the limit of complexity of coding that you would want to take on. One advantage of this script is that you can use the simpler to code Method 1 to test the more complex code for the Method 2 if you choose to implement it as a CASE statement. If you have another method you would like to contribute, feel free. Also, if anyone wants to post performance test results, that would be nice. print 'Create table to hold test data' create table #t ( number int not null primary key clustered, Val1 int, Val2 int, Val3 int, Val4 int ) GO print 'Load test data' insert into #t select number, -- Generate random numbers -- with about 1/7th null case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end, case when abs(checksum(newid()))%7 = 0 then null else checksum(newid())%1000000 end from -- Load one million rows of test data. -- Number table function here -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 dbo.F_TABLE_NUMBER_RANGE(1,1000000) go print 'Find rows that do not match for Method 1 and Method 2' select out1.*, out2.* from ( -- Method 1, using a subquery with a max select a.number, a.Val1, a.Val2, a.Val3, a.Val4, [Max_of_Val1_to_Val4] = ( select X1= max(bb.xx) from ( select xx = a.Val1 where a.Val1 is not null union all select xx = a.Val2 where a.Val2 is not null union all select xx = a.Val3 where a.Val3 is not null union all select xx = a.Val4 where a.Val4 is not null ) bb ) from #t a ) out1 join ( -- Method 2, using a case select a.number, a.Val1, a.Val2, a.Val3, a.Val4, [Max_of_Val1_to_Val4] = case when a.Val1 is not null and (a.Val1 >= a.Val2 or a.Val2 is null) and (a.Val1 >= a.Val3 or a.Val3 is null) and (a.Val1 >= a.Val4 or a.Val4 is null) then a.Val1 when a.Val2 is not null and (a.Val2 >= a.Val1 or a.Val1 is null) and (a.Val2 >= a.Val3 or a.Val3 is null) and (a.Val2 >= a.Val4 or a.Val4 is null) then a.Val2 when a.Val3 is not null and (a.Val3 >= a.Val1 or a.Val1 is null) and (a.Val3 >= a.Val2 or a.Val2 is null) and (a.Val3 >= a.Val4 or a.Val4 is null) then a.Val3 when a.Val4 is not null and (a.Val4 >= a.Val1 or a.Val1 is null) and (a.Val4 >= a.Val2 or a.Val2 is null) and (a.Val4 >= a.Val3 or a.Val3 is null) then a.Val4 else null end from #t a ) out2 on out1.number = out2.number where -- Look for results that do not match (out1.[Max_of_Val1_to_Val4] is null and out2.[Max_of_Val1_to_Val4] is not null) or (out1.[Max_of_Val1_to_Val4] is not null and out2.[Max_of_Val1_to_Val4] is null) or out1.[Max_of_Val1_to_Val4] <> out2.[Max_of_Val1_to_Val4] go print 'Find count of rows with different columns null' print 'Should have a rowcount of 16 to test all conditions' select Null_Column_Conditions = case when Val1 is null then 0 else 1000 end+ case when Val2 is null then 0 else 0100 end+ case when Val3 is null then 0 else 0010 end+ case when Val4 is null then 0 else 0001 end, count(*) from #t group by case when Val1 is null then 0 else 1000 end+ case when Val2 is null then 0 else 0100 end+ case when Val3 is null then 0 else 0010 end+ case when Val4 is null then 0 else 0001 end order by 1 go drop table #t Results: Create table to hold test data Load test data (1000000 row(s) affected) Find rows that do not match for Method 1 and Method 2 (0 row(s) affected) Find count of rows with different columns null Should have a rowcount of 16 to test all conditions Null_Column_Conditions ---------------------- ----------- 0 395 1 2444 10 2560 11 14760 100 2400 101 14955 110 14843 111 90206 1000 2518 1001 14857 1010 14989 1011 90256 1100 15100 1101 89659 1110 89783 1111 540275 (16 row(s) affected) CODO ERGO SUM

Union Ordering social.msdn.microsoft.com

Hello, I'm attempting to run three separate queries and have them returned as one recordset.� I need to show the top 10 customers based on the number of orders they placed for each of three months, then combine them to give a representation of which customers have been within the monthly top 10 for the overall period of three months.� My problem is when I run a count on the number of orders they have placed and order by that count, it is in ascending order which in escence shows me the bottom 10, not the top 10.� To resolve this issue, I can specify descending order.� This works fine with one query, but when I run all three queries using union statements, I can only have one order by which returns incorrect results.� Here is my query.� In this example, I get the combination of the three recordsets as expected, but each of the individual queries is not ordering descending, so I get the wrong end of the table.� I am not bound to union, but do not know where to go.� I do not believe I can use joins or concatenate queries because I have multiple where clauses.� Any help would be greatly appreciated. Please note that I am using a view to convert the cryptic field names which are used by a third party software tool into more friendly names.� We will call this view Friendly_View for this example. USE MyDB SELECT TOP 10�CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-1 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY�CustName, Location, Phone UNION SELECT TOP 10�CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-2 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY�CustName, Location, Phone UNION SELECT TOP 10�CustName, Location, Phone, COUNT(Orders) AS TotalOrders FROM Friendly_View WHERE MONTH([Order Date]) = MONTH(GETDATE())-3 AND YEAR([Order Date]) = YEAR(GETDATE()) GROUP BY�CustName, Location, Phone ORDER BY COUNT(Orders) DESC

Forms Data Controls :: Get A Gridview's Total Row Count Whilst Using Paging? forums.asp.net

i currently have a gridview with 100+ rows in it, i have paging set to 10 and am wanting to display the whole number of rows in a labeli currently have this code: Label2.Text = GridView1.Rows.Count.ToString() but that only seems to count the number of rows on the current page (10), what would i need to change / add to have it count all the rows in the gridview? im using vb

Forms Data Controls :: How To Merge Unique Rows And Discard Duplicates forums.asp.net

this probably isn't the correct place to ask but here goes. I've got a couple of datasets i've merged (set1 and set2) as below set1.Merge(set2) Is there any way to merge only unique rows and discard duplicates. I can't do a union at the sql level as i'm connecting to foxpor tables and they just bring back an error when i use union in my select statement.

Sql Help With Number Of Rows Return Value forums.asp.net

hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help SELECT     SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4 FROM         TABLE1 WHERE     (ID = nn)     UNION all  SELECT      SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3 FROM         table2 WHERE     TABLE2 = nn)

Counting Total Rows When Using GROUP BY forums.asp.net

hi, i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL) GROUP BY UserName UNION SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits AS UserVisits_1 WHERE (ProductID = @ProductID) AND (UserName IS NULL) GROUP BY AnonimIP this will return something like: zuperboy90 - 4 visits ANONIMOUS - 6 visits 85.104.103 - 2 visits etc how can i count the rows returned in both selections (4+6+2 = 12) ? thank you

Count Number Of Selected Rows www.ozgrid.com

How do you count know the numbers of rows that has a selection

EXTRACT ROWS FROM MULTIPLE TABLES TO FLAT FILE forums.asp.net

I'm trying to create a flat file...I need to pull a single row from one table(table1), then a single row from a second table(table2) then multiple rows from a third table(table3). I'm having problems using the UNION ALL statement because it requires that each table have the same number of columns ( I can use filler columns but then I don't want them to print to the flat file). Also I have the problem of a column--for instance col 8, table1--that has a field length of 3, but it gets several spaces added in the flat file to bring it into conformity with column 8 from table 3 which has a field length of 22. Is there a way I can SQL query to pull these needed rows from different tables into a flat file without regard to their column counts or field sizes? Thanks,

Displaying Zero's www.vbcity.com

Code: Dim col As Longcol = 1Dim cel As RangeDim rngCase As RangeDim a As LongDim lbltemp As Objecta = 0ActiveWorkbook.Sheets(1).SelectFor Each rngCase In Sheets("Data").Range(Sheets("Data").Cells(2, 7), Sheets("Data").Cells(65536, 7).End(xlUp))    For Each cel In Range(Cells(2, col), Cells(65536, col).End(xlUp))        Select Case cel.Value            Case rngCase.Value                If selrange(a + rngCase.Row - 1) Is Nothing Then                    Set selrange(a + rngCase.Row - 1) = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                    BacklogFinder.Controls.Item("lbl" & rngCase.Value & "total").Caption = "1"                Else                    Set selrange(a + rngCase.Row - 1) = Union(selrange(a + rngCase.Row - 1), Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                    BacklogFinder.Controls.Item("lbl" & rngCase.Value & "total").Caption = selrange(a + rngCase.Row - 1).Count / 52                End If                    Select Case cel.Offset(0, 10).Value ' offset 10 columns to right                        Case 0 To 4                            If selrange(a + rngCase.Row) Is Nothing Then                                Set selrange(a + rngCase.Row) = Range(Cells(cel.Row, 1), Cells(cel.Row, 52))                                BacklogFinder.Controls.Item("lbl" & rngCase.Value & "04").Caption = "1"                            Else                                Set selrange(a + rngCase.Row) = Union(selrange(a + rngCase.Row), Range(Cells(cel.Row, 1), Cells(cel.Row, 52)))                                BacklogFinder.Controls.Item("lbl" & rngCase.Value & "04").Caption = selrange(a + rngCase.Row).Count / 52                                                        End IfThis is a sample of the loop i use to create a sample sheet, it works perfect but when something is not found I would like it to set the label caption to 0(Zero)Im struggling with this as I wrote this a while ago and can't think how to add in this addition. I've tried various unseccesful ways of adding a sub if statement to the second else statement of each part with a rule like if <0 then caption = 0 with no luck.Any help would be great,thanksALEdited by - Geof on 10/17/2007 6:33:41 AM

Get Record Count And Current Row Number ADODB Recordset www.xtremevbtalk.com

How do i get the record count and current row number from an ADODB recordset?ThanksGK

Count Rows In A Listview www.codeguru.com

How can I count the number of rows in a listview and loop through them?Snowball

Combine Entry Count In A UNION Query? stackoverflow.com

How do i combine the counts from all the tables being used in a UNION query. This is what i have: $query = "SELECT COUNT(*) as num from table_one LEFT JOIN table_constant on table_one.c_id = table_constant.c_id where table_constant.user_id = '$uid' UNION SELECT COUNT(*) as num from table_two LEFT JOIN table_constant on table_two.c_id = table_constant.c_id [Code]...

Label Text Doesn't Update Programmatically? forums.asp.net

I'm trying to display a message in a label based on the number of rows in Gridview The Gridview.rows.count value seems Ok, everything seems Ok but the label text doesn't update The code I'm using is GridView1.DataBind() MsgBox(GridView1.Rows.Count.ToString) If GridView1.Rows.Count > 0 Then MsgBox(">0") ErrLbl.Text = "Rows to return" ElseIf GridView1.Rows.Count = 0 Then MsgBox("=0") ErrLbl.Text = "No rows to return" End If This is all done in a button click event ErrLbl is visible etc The text appears Ok first time but not on subsequent button clicks

From A Value Select A Range Then Find The Specify The Highest Figure www.excelforum.com

I am trying to work out how to select a range from a formula. One formula works out when a specific number in a list of rows is reached, and returns the number of values it counted before it reached the number. MATCH(TRUE,INDEX($A$2:$A$50>=B1,0),0) With the figure returned from that can I then select the range from the first row, to the number of rows counted. And with that selection find the highest number within that range, specifying the highest number as the result?

Maximum UNION Statements In A Query bytes.com

Wondering if there is a physical or realistic limitation to the number of UNION statements I can create in a query? I have a client with approx 250 tables - the data needs to be kept in seperate tables, but I need to be filtering them to create single results sets. Each table holds between 35,000 - 150,000 rows. Should I shoot myself now? lq

Insert And Update At Same Time? www.phpbuilder.com

i need to count the number items in an album now on the album i have create a field called count which counts the number items in that album but now i dnt knw how to update those as soon insert a item in the table item so i thought woul be possible to create a script that when insert data into item tables also update album table row count by adding one like update count with A now A equal row count ++1 minus if delete/ $sql = "INSERT INTO " .PHOTOS_TABLE. " ( photo_name, photo_date, [Code].....

Insert And Update At Same Time And Add ++? www.sitepoint.com

i need to count the number items in an album now on the album i have create a field called count which counts the number items in that albumbut now i dnt knw how to update those as soon insert a item in the table itemsi thought woul be possible to create a script that when insert data into item tables also update album table row count by adding one like update count with A now A equal row count ++1 minus if delete any help on start of it if possible???/

Count Number Of Commas In A Single Line? www.excelforum.com

I am trying to count the number of commas in each line of my Excel file. So I first decided to count the total number of rows and then count the number of commas in each row. I wrote this code. But I keep getting an error saying Next without for. Sub Give_The_Last_Row() Selection.SpecialCells(xlCellTypeLastCell).Select LastRow = ActiveCell.Row [Code]....

Count Block Of Numbers? ozgrid.com

I have a column of numbers of approx 3000 rows and the number is either a 0 or 1 and they are in a random order in each row. I would like to count the number of 0 in the column. If there is consecutive 0 (a block of 0), I would still need to count it as a single occurrence. For example, the count (or sum) for the number of occurrences for the number 0 in the below would be 4. 0 1 1 0

What Row Count Transformation Can Do? social.msdn.microsoft.com

There is "Row Count" transformation in SSIS. It can count the number of records. But how can I get the output from the row count? Can I insert the number of row count into a table in SSIS? Thanks for help! Mike

Using INSERT Where There Is A Multiple Value www.sqlteam.com

Hi I am trying to do a similar thing to what I did before but now using the following command. Basically I want to INSERT the clauses shown in the INSERT statement into the VALUES as shown but where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values. Do I need to somehow use UNION or UNION ALL for this? I am getting the following error when I run the statement as shown below:- Cannot insert duplicate key row in object 'communications' with unique index 'c2962cn7081'. The statement has been terminated. declare @ComNum int set @ComNum = (select max(communication_number)+1 from communications) insert into [communications] (address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number) select address_number, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum from communications where address_number in (126, 127, 128) Thanks for all your help. Jon

UNION ALL - LEFT JOIN - WEIRD www.sqlteam.com

Simple request and I am going nuts! Simple Explanation: I have a table with numbers 1-5, I want to left join a table with numbers 1-10, and show a sum... So since numbers 6-10 will not match anything, I will have a sum of zero for those rows. Simple, I thought! SELECT SUM(ISNULL(RI.quantity,0)) AS Quantity, usr.conversionRate, usr.CorpType FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr LEFT OUTER JOIN Items I ON I.ConversionRate = usr.conversionRate LEFT OUTER JOIN Requests_Items RI ON I.ID = RI.itemID AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008' LEFT OUTER JOIN Corporations_Requests CR ON CR.requestID = RI.requestID LEFT OUTER JOIN Corporations C ON C.CorpID = CR.corporationID AND C.CorpType = usr.CorpType GROUP BY usr.CorpType, usr.conversionRate ORDER BY usr.CorpType, usr.conversionRate I have no clue why this doesn't work. It appears that the UNION ALL(s) are being crossed joined. Any light shed on this would be greatly appreciated!

Proble With Count social.msdn.microsoft.com

I have a table, the table contains a data of a Site and a monitor which monitors a site, A monitor picks certain sites quarterly to do monitoring, for instance monitor k picks site1, site2, site3 to do monitoring in the first quarter of the year 2008 then for the second quarter he picks some new sites say site4 and site5 and can also pick some site which he monitored in the previous quarters asy site1. My problem is I want to get how many new sites did the monitor do monitoring for that quarter. for example monitor monitored site s1, s2, s3 in 1Q2008 and site s3, s4 in 2Q2008 the count shoul look like 1Q2008 3 2Q2008 1 because he monitored 3 new sites in 1Q2008, but in 2Q2008 he picked one new and one already reviewed site s3 we only count the new ones hope I didn't confuse you here is the table structure create table #siteMonitor (monitor varchar(100), quarter varchar(100), site varchar(100)) insert into #siteMonitor (monitor, quarter, site) select '1Q 2005', 'john moyes', 's0001' union all select '1Q 2005', 'john moyes', 's0002' union all select '1Q 2005', 'john moyes', 's0003' union all select '1Q 2005', 'john moyes', 's0004' union all select '2Q 2005', 'john moyes', 's005' union all select '2Q 2005', 'john moyes', 's003' union all select '2Q 2005', 'john moyes', 's006' union all select '1Q 2006', 'lori kumar', 's0007' union all select '1Q 2006', 'lori kumar', 's0008' union all select '1Q 2006', 'lori kumar', 's0009' union all select '1Q 2007', 'lori kumar', 's007' union all select '1Q 2007', 'lori kumar', 's0010' union all select '1Q 2007', 'lori kumar', 's0011' union all select '2Q 2007', 'lori kumar', 's007' union all select '2Q 2007', 'lori kumar', 's0012' The record set should look like monitor quarter count john moyares 1Q 2005 4 john moyares 2Q 2005 2 lori kumar 1Q 2006 3 lori kumar 1Q 2007 2 lori kumar 2Q 2007 1 Thanks

Count Number Of Rows In SELECT Query With PDO? stackoverflow.com

From the PDO manual: PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. I found that out only very recently. I had just changed my db abstraction layer to not use SELECT COUNT(1) ... anymore, because jsut quering the actual rows and then counting the result would be much more efficient. And now PDO doesn't support that!? I don't use PDO for MySQL and PgSQL, but I do for SQLite. Is there a way (without completely changing the dbal back) to count rows like this in PDO? In MySQL, this would be something like this: $q = $db->query('SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele'); $rows = $q->num_rows; // and now use $q to get actual data With the MySQLi and PgSQL drivers, this is possible. With all PDO it isn't!? extend the SQLResult->count method (my own) to replace SELECT ... FROM by SELECT COUNT(1) FROM and just return that number (very inefficient, but only for SQLite PDO). That's not good enough though, because in the example query above is a GROUP BY, which would change the meaning/function of the COUNT(1).

Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement forums.asp.net

I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN SELECT Code, COUNT(Code)as exprCount1a FROM dbo.[Test] WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code END The results of the statement: Code | exprCount1a  1              2 2              4 4              1 I would like the following results: Code | exprCount1a  1              2 2              4 3              0 4              1 Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results. Thank you in advance

Find Number Of SQL Servers Exists In The Network forums.databasejournal.com

is there any way to find the number of sqlservers exists (count and name of server) in a network using sql statement or stored procedure

Dynamic Arrays www.xtremevbtalk.com

Hello,I am wondering if someone could help me with a question. I am using VBA to count total rows in a spreadsheet, and upon find the MAX number, returning the last row's data. The dilemma is, however, that this data source will continually grow in size. The static method is OK if you always know the range, but it later dawned on me that this is a test source only; production data will be CONSIDERABLY larger. I am toying with the idea of using a dynamic array to do this. I am pretty new to VBA and thought I'd ask for some input. The code below is pretty self explanatory. I am using relative references to do this below. Appreciate any help you can provide!__________________________________________________ owb.Worksheets("tester").Select With ActiveSheet.Range("A1:H500") .Select Worksheets.Add Worksheets("Sheet1").Range("A1").Cells.Value = "Number of rows from A" Worksheets("sheet1").Range("B1").Cells.Value = "=COUNT(tester!R[1]C[-1]:R[500]C[-1])" Worksheets("Sheet1").Range("A2").Cells.Value = "Number of rows from B" Worksheets("sheet1").Range("B2").Cells.Value = "=COUNT(tester!RC:R[499]C)" Worksheets("Sheet1").Range("A3").Cells.Value = "Number of rows from C" Worksheets("sheet1").Range("B3").Cells.Value = "=COUNT(tester!R[-1]C[1]:R[498]C[1])" Worksheets("Sheet1").Range("A4").Cells.Value = "Number of rows from D" Worksheets("sheet1").Range("B4").Cells.Value = "=COUNT(tester!R[-2]C[2]:R[497]C[2])" Worksheets("Sheet1").Range("A5").Cells.Value = "Number of rows from E" Worksheets("sheet1").Range("B5").Cells.Value = "=COUNT(tester!R[-3]C[3]:R[496]C[3])" Worksheets("Sheet1").Range("A6").Cells.Value = "Number of rows from F" Worksheets("sheet1").Range("B6").Cells.Value = "=COUNT(tester!R[-4]C[4]:R[495]C[4])" Worksheets("Sheet1").Range("A7").Cells.Value = "Number of rows from G" Worksheets("sheet1").Range("B7").Cells.Value = "=COUNT(tester!R[-5]C[5]:R[494]C[5])" Worksheets("Sheet1").Range("A8").Cells.Value = "Number of rows from H" Worksheets("sheet1").Range("B8").Cells.Value = "=COUNT(tester!R[-6]C[6]:R[493]C[6])" 'Worksheets("Sheet1").Range("A9").rows.value 'Worksheets("sheet1").Range("B9").Cells.Value = "=Max(R[-7]C:R[-2]C)"

Union www.vbforums.com

How can I do UNION statement on MS Access?

Insert Row Count Into Range Reference? www.mrexcel.com

I want to apply conditional formatting to a range, but only to the number of rows in the sheet (to avoid formatting blank rows). I am not sure how to insert the row count into the range select. NumRows = Application.WorksheetFunction.CountA(Range("A1:A65536")) gives me the number of rows, but how do I get that into Range("A5:F18").Select 18 being the number I want to substitute the row count for.

Databases :: How To Take Union With Prepared Statement forums.asp.net

I created a stored procedure and written two query strQuery1 and strQuery2 now i want to take union of these two query using prepared statement

Sum Every Third Row, Then / Count Every Third Row www.mrexcel.com

I'm trying to sum every third row, and then divide by the count on the number of entries in those rows.

Locate Last Used Row & Add Formula Via Code www.ozgrid.com

How can I program a variable 'rowcountnumber', that would count the number of active rows, starting from row 10 ?

Get Number Of Results From SqlDataSource forums.asp.net

Hi all, Is there a way of counting the number of results/rows from an SqlDataSource which uses a select statement like: "SELECT * FROM TABLE1". I need to get this value from the SqlDataSource into the Sub Page_Load. I don't want to bind to any gridviews, repeaters etc. I just want to get the number of rows/results from the SqlDataSource. Is there a way of doing this? Thanks  

Getting Number Of Rows www.dbforums.com

Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

Count Of Rows Updated www.xtremevbtalk.com

Is there a way to return the number of rows updated by an SQL update statement that is run using the currentdb.execute() statement?

Union Query Help www.xtremevbtalk.com

After reading several threads about union queries I came up with this:"SELECT Sum(views) AS SumViews1 FROM Vehicles Where O_AirBag = 'true' UNION SELECT Sum(views) AS SumViews2 FROM Vehicles Where O_DualAirBag = 'true'"but it errors out. If I run it without the union, and run each sql statement seperately they run fine. Why isn't the union working? Any ideas?

Update Query With Mysql 4.0 phpbuilder.com

I have MySQL 4.0 (so no subqueries please) and I have one table with distinct phone numbers, and one table with six-months of call records. I want to count the occurences of each number in table A using the call records in table B. But, in the same query I want to UPDATE the phone number row in table A with that count. I just ran one query to count the phone number occurences in table B and another query to update table A. The problem is that it takes forever because table B has 973,000 rows of data.

How Do I Put Records Number In Union social.msdn.microsoft.com

� Hi i have sql statement like this : � SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b � the problem is the rec_num repeat for each statement like this : � rec_num������������� empname �1����������������������� john �2������������������������maggy 1������������������������ lee 2������������������������ mary 3�������������������������louis � How do i make the rec_num continue for the next statement after union.

Add Listview Row Count To Label? www.vbforums.com

I was wondering how you could have a label keep the number count on the number of rows on a listview?

Have A Better UPDATE Statement Than What I Wrote? bytes.com

/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an Update statement. I have a sample code to reproduce my problem. To simplify the scenario I am trying to use Order related tables to explain a little better the tables i have to work with. Please don't bother looking at the wrong relationship and how the tables are designed. That's not my current problem. My job is to correct the wrong data either using code or manually. Here are the tables I have created: TBLORDERS where two fields I am interested in are: ORDERTYPENO linking to TBLORDERTYPE LASTSTATUSNO linking to TBLSTATUS TBLORDERTYPE where one field I am interested in is ORDERPROCESSINGNO TBLORDERPROCESSING Each order has a link to OrderTypeNo and each OrderTypeNo has a link to OrderProcessingNo. TBLORDERSTATUSES where one field I am interested in is STATUSNO TBLSTATUS where one field I am interested in is ORDERPROCESSINGNO I have the sample code here: */ --DROP DATABASE TestDB CREATE DATABASE TestDB GO USE TestDB CREATE TABLE TBLORDER ( IDNO INT PRIMARY KEY NOT NULL, ORDERNUMBER VARCHAR(50), ORDERTYPENO INT, LASTSTATUSNO INT ) INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO) SELECT 1, 'ORDERTEST1', 1, 3 UNION ALL SELECT 2, 'ORDERTEST2', 1, 3 UNION ALL SELECT 3, 'ORDERTEST3', 2, 16 UNION ALL SELECT 4, 'ORDERTEST4', 2, 16 UNION ALL SELECT 5, 'ORDERTEST5', 2, 16 UNION ALL SELECT 6, 'ORDERTEST6', 2, 16 UNION ALL SELECT 7, 'ORDERTEST7', 4, 5 UNION ALL SELECT 8, 'ORDERTEST8', 4, 5 UNION ALL SELECT 9, 'ORDERTEST9', 6, 22 UNION ALL SELECT 10, 'ORDERTEST10', 6, 22 UNION ALL SELECT 11, 'ORDERTEST11', 7, 20 CREATE TABLE TBLORDERSTATUSES ( IDNO INT PRIMARY KEY NOT NULL, ORDERNO INT, STATUSNO INT ) INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO) SELECT 1, 1, 1 UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 3 UNION ALL SELECT 4, 1, 4 UNION ALL SELECT 5, 2, 1 UNION ALL SELECT 6, 2, 2 UNION ALL SELECT 7, 2, 3 UNION ALL SELECT 8, 2, 4 UNION ALL SELECT 9, 3, 15 UNION ALL SELECT 10, 3, 16 UNION ALL SELECT 11, 3, 17 UNION ALL SELECT 12, 4, 15 UNION ALL SELECT 13, 4, 16 UNION ALL SELECT 14, 4, 17 UNION ALL SELECT 15, 5, 15 UNION ALL SELECT 16, 5, 16 UNION ALL SELECT 17, 5, 17 UNION ALL SELECT 18, 6, 15 UNION ALL SELECT 19, 6, 16 UNION ALL SELECT 20, 6, 17 UNION ALL SELECT 21, 7, 5 UNION ALL SELECT 22, 7, 6 UNION ALL SELECT 23, 8, 5 UNION ALL SELECT 24, 8, 6 UNION ALL SELECT 25, 9, 22 UNION ALL SELECT 26, 9, 23 UNION ALL SELECT 27, 9, 24 UNION ALL SELECT 28, 9, 25 UNION ALL SELECT 29, 10, 22 UNION ALL SELECT 30, 10, 23 UNION ALL SELECT 31, 10, 24 UNION ALL SELECT 32, 10, 25 UNION ALL SELECT 33, 11, 18 UNION ALL SELECT 34, 11, 19 UNION ALL SELECT 35, 11, 20 UNION ALL SELECT 36, 11, 21 CREATE TABLE TBLORDERTYPE ( IDNO INT PRIMARY KEY NOT NULL, ORDERTYPE VARCHAR(50), ORDERPROCESSINGNO INT ) INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO) SELECT 1, 'CATEGORY 100', 1 UNION ALL SELECT 2, 'CATEGORY 200', 5 UNION ALL SELECT 3, 'CATEGORY 300', 3 UNION ALL SELECT 4, 'CATEGORY 400', 2 UNION ALL SELECT 5, 'CATEGORY 500', 4 UNION ALL SELECT 6, 'CATEGORY 600', 9 UNION ALL SELECT 7, 'CATEGORY 700', 8 UNION ALL SELECT 8, 'CATEGORY 800', 7 UNION ALL SELECT 9, 'CATEGORY 900', 6 CREATE TABLE TBLORDERPROCESSING ( IDNO INT PRIMARY KEY NOT NULL, ORDERPROCESSING VARCHAR(50) ) INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING) SELECT 1, 'ORDER PROCESSING A1' UNION ALL SELECT 2, 'ORDER PROCESSING A9' UNION ALL SELECT 3, 'ORDER PROCESSING Z5' UNION ALL SELECT 4, 'ORDER PROCESSING 76' UNION ALL SELECT 5, 'ORDER PROCESSING 98' UNION ALL SELECT 6, 'ORDER PROCESSING AB' UNION ALL SELECT 7, 'ORDER PROCESSING 11' UNION ALL SELECT 8, 'ORDER PROCESSING T7' UNION ALL SELECT 9, 'ORDER PROCESSING ZX' CREATE TABLE TBLSTATUS ( IDNO INT PRIMARY KEY NOT NULL, STATUS VARCHAR(50), ORDERPROCESSINGNO INT ) INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO) SELECT 1, 'ABC', 1 UNION ALL SELECT 2, 'DEF', 1 UNION ALL SELECT 3, 'GHI', 1 UNION ALL SELECT 4, 'JKL', 1 UNION ALL SELECT 5, 'MNO', 2 UNION ALL SELECT 6, 'PQR', 2 UNION ALL SELECT 7, 'STU', 3 UNION ALL SELECT 8, 'VWX', 3 UNION ALL SELECT 9, 'YZ', 3 UNION ALL SELECT 10, '123', 3 UNION ALL SELECT 11, '456', 3 UNION ALL SELECT 12, '789', 3 UNION ALL SELECT 13, '0AA', 3 UNION ALL SELECT 14, '0BB', 3 UNION ALL SELECT 15, '0CC', 5 UNION ALL SELECT 16, '0DD', 5 UNION ALL SELECT 17, '0EE', 5 UNION ALL SELECT 18, '0FF', 8 UNION ALL SELECT 19, '0GG', 8 UNION ALL SELECT 20, '0HH', 8 UNION ALL SELECT 21, '0II', 8 UNION ALL SELECT 22, '0JJ', 9 UNION ALL SELECT 23, '0KK', 9 UNION ALL SELECT 24, '0LL', 9 UNION ALL SELECT 25, '0MM', 9 /* If you run the above, the data is CORRECT and the way it normally should be. Basically, each Order is linked to an OrderTypeNo. Each OrderTypeNo is linked to an OrderProcessingNo. Each Order has MANY OrderStatuses. Each OrderProcessingNo has MANY Statuses. So both TBLORDERTYPE and TBLSTATUS is pointing to TBLORDERPROCESSING. I will mess up an Order record for example to point to a wrong OrderType and leave its LASTSTATUSNO and all its CHILD TBLORDERSTATUSES STATUS records point to the CORRECT ORDERPROCESSINGNO. */ UPDATE TBLORDER SET ORDERTYPENO = 3 WHERE IDNO = 5 OR IDNO = 10 /* So now both Order IDNO 5 & 10 are basically messed up as they are pointing to ORDERTYPENO 3 (i.e. ORDERPROCESSINGNO 3) whereas their LASTSTATUSNO and all its TBLORDERSTATUS STATUS records are pointing to ....... ORDERPROCESSINGNO 5 & 9 Now I will mess up both TBLORDER and TBLORDERDETAILS in order for my code NOT to fix it since this I will have to deal and decide what to correct manually. */ UPDATE TBLORDER SET ORDERTYPENO = 2, LASTSTATUSNO = 15 WHERE IDNO = 8 SELECT * FROM TBLORDER GO CREATE VIEW VIEW1 -- This VIEW1 returns all TBLORDER records that have the problem AS SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO FROM TBLORDER INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO GO CREATE VIEW VIEW2 -- This VIEW2 does a GROUP BY of all TBLORDER.IDNO & TBLSTATUS.ORDERPROCESSINGNO AS SELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO FROM TBLORDERSTATUSES INNER JOIN TBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNO INNER JOIN TBLSTATUS ON TBLORDERSTATUSES.STATUSNO = TBLSTATUS.IDNO INNER JOIN VIEW1 ON TBLORDER.IDNO = VIEW1.IDNO GROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO ORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO GO CREATE VIEW VIEW3 -- This VIEW3 checks to see if TBLORDERSTATUS records have more than one ORDERPROCESSINGNO AS SELECT IDNO FROM VIEW2 GROUP BY IDNO HAVING (COUNT(*) > 1) GO CREATE TABLE TMPORDERS ( IDNO INT, OLDORDERTYPENO INT, NEWORDERTYPENO INT, LASTSTATUSNO INT ) INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO) SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO FROM TBLORDER INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO LEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULL SELECT * FROM TMPORDERS UPDATE TMPORDERS SET NEWORDERTYPENO = TBLORDERTYPE.IDNO FROM TBLORDERTYPE INNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO = TBLORDERPROCESSING.IDNO INNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO = TBLSTATUS.ORDERPROCESSINGNO WHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNO UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO FROM TMPORDERS WHERE TMPORDERS.IDNO = TBLORDER.IDNO SELECT * FROM TBLORDER /* Is there a better to write my Update statement? As you can see that I am using 3 views, 1 temp table and 2 update statements to fix my problem. I am not even sure if i'll need to add more update statements to handle other corrections. If that is the case I am trying to see if my code can be simplified in order for it to be easily modifiable to handle other scenarios. Thank you for your time. */

Count Numbers In Sequence .... www.ozgrid.com

I have a list of random numbers in column A the number range is from 0 to 20,000. What I am trying to do is to count the sequential numbers after each random number....sorry this is not really explained well so I willl use a numeric example. A 23 24 25 33 34 60 77 80 Above is what I have in column A you can see that my number range starts a 23 so I would like to count the sequential numbers that occur from 23 in this case it would be count(23,24,25) giving the answer 3 I would like this answer to be inserted into column B next to 23 and the numbers that were counted in this formula then be deleted as rows (i.e. where the number 24 and 25 was counted then the rows containing these numbers are deleted), The macro moves then to count the next number. In the case of just a single number then a value of 1 is entered into the column next to the number indicating that there was only one number in that sequence.