Logo

Advertisement

Count The Number Of Rows In A UNION ALL Statement

bytes.com

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

View Answers
Very similar questions

Advertisement

Advertisement

Number Table Function www.sqlteam.com

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

Loop && Count www.vbcity.com

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

Get Count From Union Select bytes.com

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

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   

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

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

Union www.webmasterworld.com

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

Count From Two Tables www.phpfreaks.com

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

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

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

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

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

Advertisement

If Else Operators phpbuilder.com

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

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

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

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

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

Grouping / Reporting Question www.sqlteam.com

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

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

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

Table Variable (bug)? www.sqlteam.com

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

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.

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

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

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

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

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

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.

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)

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?

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

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

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

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

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!

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

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.

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.

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

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?

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

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

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

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

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"

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

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

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

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

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.

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

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?

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

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

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,

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

More - Aggravation Over Aggregates www.sqlteam.com

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

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

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

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

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)

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

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.

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?

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

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

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.

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

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

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