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

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!

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.

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

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 !!!

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

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).

Advertisement

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).'

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'] }

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

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.

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

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.

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.

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 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"

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

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?

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!

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?

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

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

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

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.

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?

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

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].....

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.

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

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]...

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)

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

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

Count Number Of Selected Rows www.ozgrid.com

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

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

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,

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

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

Union www.vbforums.com

How can I do UNION statement on MS Access?

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

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH... social.msdn.microsoft.com

Dear All � I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor. � Here is my query sample for yours to understand. � � SET NOCOUNT ON DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000) Set @qstring = 'select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union� select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm union select itemcode from oitm' PRINT '-------- ITEM Products Report --------' DECLARE ITEM_cursor CURSOR FOR execute (@qstring) OPEN ITEM_cursor FETCH NEXT FROM ITEM_cursor INTO @ITEMCODE WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' SELECT @message = '----- Products From ITEM: ' + @ITEMNAME PRINT @message -- Get the next ITEM. FETCH NEXT FROM ITEM_cursor INTO @ITEMcode END CLOSE ITEM_cursor DEALLOCATE ITEM_cursor � Why i use @qstring? It is because the query will be changed by different critiera. � Regards Edmund

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?

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

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???/

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?

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

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

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.

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

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).

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 ?

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?

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)"

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.

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.

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  

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?

How To Wrap A UNION Query In A Totals Query? bytes.com

I've got some SQL that works as far as returning a recordset from a series of UNION statements. viz: SELECT whatever UNION this UNION that UNION other Now I want to group and sum on it's results. Started out tying: SELECT * FROM ( union stuff ) ....but couldn't even get past the syntax check. Where I'm headed is a sort of pivot table presentation of some hours data associated with various projects with a column for each of six date ranges. Bottom line: can somebody give me a pointer to the syntax needed to wrap those UNION statements and then select/group/sum their results? -- PeteCresswell

Rows With Specific Ids www.webmasterworld.com

I have many rows in my database, all with ids which are not unique. I would like to count these ids and display the numbers in a loop. Like so. Row 1 (67) Row 2 (34) Row 3 (234) All ids that match the id of the row should be counted and displayed. I have tried this. $count = mysql_result(mysql_query("SELECT COUNT(*) FROM table WHERE $id=id"), 0);

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?

Display A Union Of Ranges In A Spreadsheet Control : Solution Inside www.vbcity.com

Hi All.How to display a group of ranges from various columns in a spreadsheet control on a form.Code:    ActiveWorkbook.Sheets(1).Select    Dim wb As Workbook    Set wb = ActiveWorkbook        Dim rng1 As Range    Dim rng2 As Range    Dim rng3 As Range    Dim rng4 As Range    Dim rng5 As Range    Dim rng6 As Range    Dim rng7 As Range    'set ranges    Set rng1 = wb.Sheets("Test").Range("L1:L" & Range("L" & Range("L:L").Rows.Count).End(xlUp).Row)    Set rng2 = wb.Sheets("Test").Range("S1:S" & Range("S" & Range("S:L").Rows.Count).End(xlUp).Row)    Set rng3 = wb.Sheets("Test").Range("W1:W" & Range("W" & Range("W:W").Rows.Count).End(xlUp).Row)    Set rng4 = wb.Sheets("Test").Range("X1:X" & Range("X" & Range("X:X").Rows.Count).End(xlUp).Row)    Set rng5 = wb.Sheets("Test").Range("Y1:Y" & Range("Y" & Range("Y:Y").Rows.Count).End(xlUp).Row)    Set rng6 = wb.Sheets("Test").Range("AK1:AK" & Range("AK" & Range("AK:AK").Rows.Count).End(xlUp).Row)    Set rng7 = wb.Sheets("Test").Range("AL1:AL" & Range("AL" & Range("AL:AL").Rows.Count).End(xlUp).Row)        'Application.Union(rng1, rng2, rng3, rng4, rng5, rng6, rng7).Select    'This union cannot be used due to the limitations of excel.    rng1.Select    rng1.Copy    frm1.Spreadsheet1.ActiveSheet.Range("A1").Paste        rng2.Select    rng2.Copy    frm1.Spreadsheet1.ActiveSheet.Range("B1").Paste        rng3.Select    rng3.Copy    frm1.Spreadsheet1.ActiveSheet.Range("C1").Paste        rng4.Select    rng4.Copy    frm1.Spreadsheet1.ActiveSheet.Range("D1").Paste        rng5.Select    rng5.Copy    frm1.Spreadsheet1.ActiveSheet.Range("E1").Paste        rng6.Select    rng6.Copy    frm1.Spreadsheet1.ActiveSheet.Range("F1").Paste        rng7.Select    rng7.Copy    frm1.Spreadsheet1.ActiveSheet.Range("G1").PasteAfter hours of trying to find a method, the solution althouh ugly works. It happens because Excel doesn't like selecting from non- adjacent cells.Errors includeIn Microsoft Excel, when you attempt to copy nonadjacent cell selections, you may receive one of the following error messages. Microsoft Excel 97 for WindowsThat command cannot be used on multiple selections. Microsoft Excel Versions 4.0, 5.0 and 7.0Cannot do that command on nonadjacent selections Microsoft Excel Versions 3.0 and EarlierCan't do that command on a multiple selection These error messages may occur when your cell or range selections are in different columns or different rows. You can, however, copy and paste nonadjacent ranges without an error message if your selection contains any of the following:  Multiple rows within the same column, for example, A1, A3, A5 • Multiple columns within the same row, for example, A2, C2, E2 • Multiple rows and columns within the same columns and rows, for example, A1, C1, A3, C3, A5, C5 NOTE: This message also occurs if nonadjacent sheets are selected in a Microsoft Excel 5.0 or later workbook, and you are pasting to another workbook. The above solutions is a guide, you can improve on it unfortunatly the application.union doesn't work in most cirumstances.AlEdited by - Big Al Inc on 8/21/2007 5:42:07 AM

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.

Problem With Query On A View Created By A Union forums.databasejournal.com

Hi, I have a view V1 created as a plain UNION from 2 tables: SELECT * FROM T1 UNION SELECT * FROM T2 If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned. However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows. Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it? thx, smiley61

Counting Numbers And Colours Within Cell? www.mrexcel.com

I wish to count the "number" of numbers in a cell i.e. Cell A1 has the following data: 12 25 25 This should return 3 Simillarly 4546546545646456 1 2 Should also return 3 I will have data such as the above in a row so i need the count to be able to count a range as opposed to the single cell example above. Furthermore I need a separate count to count the number or red numbers in a cell. Same parameters as above.

Limit To The Number Of Rows A Dataset Can Store? www.dbforums.com

hI, I am using visual c# 2003 and sqlserver 2000 and i am trying to query a column in the sql server and store it into a dataset but i got an error msg: The number of rows for this query will output 90283 rows. -------------------------------------------------------------------------------- Query : SELECT L_ExtendedPrice, COUNT (*) AS Count FROM LINEITEM GROUP BY L_ExtendedPrice ORDER BY Count DESC"; --------------------------------------------------------------------------------- Error msg : An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. ---------------------------------------------------------------------------------- is there a limit to the number of rows a dataset can store?

Opening Empty Recordset www.vbforums.com

Hello,I have following SQL string:SELECT BPSName, SUM(cnt) FROM (SELECT BPS.BPSName, COUNT(*) AS cnt FROM tblBPS BPS JOIN tblManagerFeedback MF ON BPS.BPSID = MF.BPSID GROUP BY BPS.BPSName Union All SELECT BPS.BPSName, COUNT(*) FROM tblBPS BPS JOIN tblNewHireEval NHE ON BPS.BPSID = NHE.BPSID GROUP BY BPS.BPSName Union All SELECT BPS.BPSName, COUNT(*) FROM tblBPS BPS JOIN tblProjectPerformance PP ON BPS.BPSID = PP.BPSID GROUP BY BPS.BPSName Union All SELECT BPS.BPSName, COUNT(*) FROM tblBPS BPS JOIN tblTrainingCoaching TC ON BPS.BPSID = TC.BPSID GROUP BY BPS.BPSName) AS BPS GROUP BY BPSnameWhen I try to fill any grid, recordset shows like EOF is true and I get no data filled in. I tried same thing with simple query like "SELECT * from tblBPS" and it works fine.Can I read fields in recordset if using Count or Group By in SQL string?Thanks in advance!

SQL 'Having' On Multiple Columns? forums.asp.net

Can I write a having statement for multiple columns? Here's my situation: I want to select duplicates from a table based off of 3 fields: Normally one would use HAVING COUNT(*) > 1 however, I need to INSERT INTO my table based on duplicates of the 3 fields but also insert the key from the first table, ie: insert into #TempTable (key, field1, field2, field3) select key, field1, field2, field3 from Table1 order by field1, field2, field3 Having COUNT(field1)>1 and COUNT(field2)>1 and COUNT(field3)>1 My question is this: Will this having statement compare all three fields of this row to all three fields of the other rows, or does it do each column independantly? for example:row 1: a   b   g                   row 2: a   c   k                   row 3: j    c   k   These rows aren't the same, even though there are 2 a's, I want it to look at the entire row, and not return all three of these.  

Count Number Of Filtered Rows www.mrexcel.com

To count the genuine number of rows in a worksheet, I use... count = Cells.Find(What:="*", SearchDirection:=xlPrevious,SearchOrder:=xlByRows).Row When I add an auto-filter, the above still works fine. When the auto-filter doesn't reduce the no. rows, the above returns a value of 1. I've tried using... count = Rows.SpecialCells(xlCellTypeVisible).Count ... with a result of 1 if no rows are filtered So, what is the best way to count the populated number of rows on a worksheet where there is an auto-filter that may, or may not, reduce the number of rows ?

Adding Count To Each Subtotal Line? www.excelforum.com

I use the Subtotal function to sum several columns during the subtotal function. I dont think I can run a subtotal doing the sums and counts. Is there any way to add the number 0f rows in each subtotal? Count? I'm not sure how to go to each blank row (subtotal line) and count/add the number 0f rows in that subtotal?

Count Hidden Rows www.ozgrid.com

i count number of all rows with: Dim countall As Long countall = Sheet1. Range("a1", Sheet1.Range("A65535").End(xlUp)).Rows.count this is the total of all rows, including hidden. how do i count the hidden rows which have been filtered? preferably not a loop, isn't there a way to use "entirerow.hidden" and count that?

Insert Multiple Rows - Performance www.sqlteam.com

hi, I came across the following topic which speaks about inserting multiple rows into a table. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52980 The following is the code it concludes: ---------------------------------------------------------- INSERT INTO InstitutionManagement.dbo.b10_partC (unitid, repyear, test, subject_area, scores, credit_awarded, comments, recno) select unitid = 'data1a', repyear = 'data1b', test = 'data1c', subject_area = 'data1d', scores = 'data1e', credit_awarded = 'data1f', comments = 'data1g', recno = 'data1h' union all select unitid = 'data2a', repyear = 'data2b', test = 'data2c', subject_area = 'data2d', scores = 'data2e', credit_awarded = 'data2f', comments = 'data2g', recno = 'data2h' union all select unitid = 'data3a', repyear = 'data3b', test = 'data3c', subject_area = 'data3d', scores = 'data3e', credit_awarded = 'data3f', comments = 'data3g', recno = 'data3h' ... And so on... ---------------------------------------------------------- My question is based on the performance of the above insert statement aganist Microsoft.Net SqlBulkCopy Class. One more thing: Does the above statement gets executed as asingle statement or as multiple statements (One execution for each Select statement). thanks regards, vinay

Access 2000 Subquery Issue www.vbcity.com

Ok, I can do the followingCode:SELECT COUNT(cq.qid)FROM completed_questions cqUNIONSELECT cq.qidFROM completed_questions cqThe problem with this on is taht it will return the count but also the cq.qids from the other query in the union.But I can't do the followingCode:SELECT COUNT(cq.cqid)FROM completed_questions cqWHERE cq.cqid IN (SELECT cq.qidFROM completed_questions cqUNIONSELECT cq.qidFROM completed_questions cq)This query should fix the above problem but it doesn't. Access tells me that "This operation is not allowed in subqueries."I could be wrong but by using simple queries like this one, i think i've come to the conclusion that UNION cannot be used in a subquery when COUNT is in the main query. How do i get around this. UNION was the only way i could combine the two queries into one. Once i've combined those queries, I want to count the number of cqids that appear. I can use COUNT and a subquery, but i can't use COUNT and UNION in a subquery. Or maybe I just can't use UNION in a subquery. Either way, i have this big, complex query that i don't want to start over on.Any ideas?

Object Required: Union Method www.ozgrid.com

columnA contains account numbers sorted in ascending order i need to use the union method to select all rows where the account number changes so that later i can insert rows between different account numbers in one action. i tried to use the following code but an error occurs stating 424 object required ....

'Alias' And 'Order By' Troubles With Union Query? www.access-programmers.co.uk

Hello all, Does anyone know (and would be willing to share) how one can order the results of a Union query in Access by something other than the returned values? I have a simple Union query that would work perfectly - if I could get the thing to order the results in a particular order everytime:mad:. The query returns counts (all from one table) of separate select statements that each meet certain conditions (e.g. having state=Nevada, having state=California, etc.) as records - but since Union queries in Access order results in ascending or descending order by returned value, it puts my records in a different order every time the record values change. I need to have them ordered the same way (in the same order as they appear in the Union query, if possible) everytime so that I can return those values to specific cells in MS Excel. Any suggestions would be much appreciated!

SQL Speed Issues www.codeguru.com

I am running a SQL query on a IBM AS/400 database with the following:SELECT COUNT(SRL) FROM LOCATE WHERE DATE = '05312001' AND (TIME IN (SELECT TIME FROM LOCATE WHERE DATE = '05312001' AND FLOC = 'SL' AND TLOC = 'KIT' AND TIME &gt; 60000))The above query takes on average 75 seconds to return. There are 1.4 million records in the databse.The following query takes only 5 seconds to return:SELECT COUNT(SRL) FROM LOCATE WHERE FLOC = 'SL' AND TLOC &lt;&gt; 'SL' AND DATE = '05312001' AND TIME &gt; 60000What I want to know is how come when I do a union and run both queries at the same time it only takes 5 seconds with the follwing query:SELECT COUNT(SRL) FROM LOCATE WHERE FLOC = 'SL' AND TLOC &lt;&gt; 'SL' AND DATE = '05312001' AND TIME &gt; 60000 UNION SELECT COUNT(SRL) FROM LOCATE WHERE DATE = '05312001' AND (TIME IN (SELECT TIME FROM LOCATE WHERE DATE = '05312001' AND FLOC = 'SL' AND TLOC = 'KIT' AND TIME &gt; 60000))Question number 2:The above query returns a count of 604 records. Now if instead of requesting a count I request the Date and Time for each record and I run just one of the queries (without the union) I get all 604 records (there are duplicate date and time combinations) but if I do the union (since this method brings the data back faster) I only get 68 records (no duplicates).How come when I request a count I get 604 but when I request the Date, Time (only when doing the union) I get 68? It looks as though it brings back only distinct records but I do not specify the DISTINCT keyword.-K