Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have part of what I am doing figured out, thanks to the help of an online blog. Now I am trying to figure out how to add a min and max value to the statement.

here is what I have now:

     Dim sqlcon As New SqlConnection("mysqlstatement")
        Dim myCmd As New SqlCommand("SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC", sqlcon)


I want to take a particular column which is the age of the people in my database and run a min and max statement tied into what is already done.

To improve on this question for clarity-

I set up a listview event that has the following fields:

table| records| age_min| age_max| database|
sales 2000 18 76 sales2016
employees 200 19 64 employees2016

the code I provided above plus this code:

sqlcon.Open()
       Dim myReader As SqlDataReader = myCmd.ExecuteReader

       While myReader.Read
           Dim newlistviewitem As New ListViewItem
           newlistviewitem.Text = myReader.GetString(0)
           newlistviewitem.SubItems.Add(myReader.GetInt32(1))
           ListView1.Items.Add(newlistviewitem)


       End While

       sqlcon.Close()


Allows me to generate information about the tables and records which is just the amount of rows per table. This information is displayed in a listview. Problem is I need to include information about the age (min/max) of that column information, but the way the original statement is written I can't. I am needing help with a possible rewrite to include all information or adding to the original code.

What I have tried:

I have tried to use the syscolumns in the statement, and labeled it sc.
so I tried to set it up using sc.name and sc.id which when I plugged in the name all I got was just that "the name", which was "age" and not the numerical values I was looking to get.
Posted
Updated 21-Feb-17 16:37pm
v2
Comments
OriginalGriff 19-Feb-17 3:56am    
It's not that clear (to me at least) exactly what you are trying to get. Perhaps if you supply sample table data and show us what you get from that query, and what you want from your DB it might help?
Use the "Improve question" widget to edit your question and provide better information.
Member 11856456 19-Feb-17 9:53am    
OrigianlGriff,

I have supplied additional information along with some data and design. I hope this helps with a clearer understanding.

You can't mix table records with aggregates over a cloumn of the same table in a single SELECT statement. The simpliest way to solve this is to join a second query with the aggregated values like this:

SQL
SELECT t.[Age]
	 , mm.[MIN]
	 , mm.[MAX]
  FROM [dbo].[<Tablename>] t
      ,(SELECT MIN([Age]) AS [MIN]
	         , MAX([Age]) AS [MAX]
         FROM [dbo].[<Tablename>]
		WHERE ([Age] IS NOT NULL)) mm
 
Share this answer
 
Comments
Member 11856456 19-Feb-17 9:56am    
NightWizzard,

The only problem with the solution that you have given is that it does not produce values for all tables, only one table. I have to have it generate age min and max along with the table name its associated with.
NightWizzard 19-Feb-17 10:05am    
I don't know your datamodel, so it's difficult to push you to the right direction. If that means, that you have to gather the min and max age from more than one table you might make use of the UNION statement to concatenate the table sources. If it means that you have to get the min max ages for changing tables from their individually related tables you should create the SQL commands dynamically with the use of variables. Hope that helps...? Otherwise it would be a good idea to improve your question with a description of all tables you want to access.
Member 11856456 19-Feb-17 10:12am    
NightWizzard,

I just changed and improved my question not to long ago in hopes to provide more clarity.
NightWizzard 19-Feb-17 11:40am    
Sorry, but from your improvement I can't understand from which tables you want to read, what age columns they contain and how the listview event will be provided with the data.
Member 11856456 19-Feb-17 22:33pm    
I am trying to get a min and max value from all tables in a database. so when I go and pick a database it will display the tables, row count, min age, max age and original database that the information is in. I want to be able to do a print out of that information.
Thought a while about your problem and here's the solution I think you search for:
1) Create a Stored Procedure like
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetTableInfos

AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Schema nvarchar(200);
	DECLARE @Table nvarchar(200);
	DECLARE @Column nvarchar(200);
	DECLARE @Cmd nvarchar(4000);
	DECLARE @TableInfos TABLE
			(
			 [SchemaName] nvarchar(200)
			,[TableName] nvarchar(200)
			,[RowCount] int
			,[MinAge] int
			,[MaxAge] int
			)

	DECLARE curLoop CURSOR LOCAL FAST_FORWARD FOR
		SELECT [TABLE_SCHEMA]
			 , [TABLE_NAME]
			 , [COLUMN_NAME]
		  FROM [INFORMATION_SCHEMA].[COLUMNS]
		 WHERE ([COLUMN_NAME] = 'Age')
		   AND ([DATA_TYPE] = 'int');

	OPEN curLoop;

	WHILE (1 = 1)
		BEGIN
			FETCH NEXT FROM curLoop INTO @Schema, @Table, @Column;

			IF (@@FETCH_STATUS <> 0)
				BREAK;

			SET @Cmd = 'SELECT ''' + @Schema + '''
							 , ''' + @Table + '''
							 , (SELECT COUNT(*) FROM [' + @Schema + '].[' + @Table + '])
							 , (SELECT MIN([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))
							 , (SELECT MAX([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))'

			 INSERT INTO @TableInfos
							 (
							  [SchemaName]
							 ,[TableName]
							 ,[RowCount]
							 ,[MinAge]
							 ,[MaxAge]
							 )
			 EXEC sp_executesql @Cmd;
		END

	CLOSE curLoop;
	DEALLOCATE curLoop;

	SELECT * FROM @TableInfos;
END
GO


You may alter the name of the column that contains the ages to aggregate or add more info columns to the result set.

2) Change your SELECT statement to get the table infos into
SQL
EXECUTE [dbo].[GetTableInfos]


This will return the result set from the stored procedure.
Hope this will fit your needs?

Good luck!
 
Share this answer
 
Quote:
How to use min and max functions in VB.NET using SQL statement

try with a SQL tutorial site

SQL MAX() Function[^]
SQL MIN() Function[^]
SQL Tutorial[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900