Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







SQL Count Records Based On 2 Columns


Newbie alrt...
 
I am trying to create an asp page that will update an Access 2000 database.  I need to update records if the user exists and create a new record if the user does not exist.  Most of the variables are pulled from a separate "post" form. 
 
I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name.  Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.

 
here is the chunk of code in question... all RF_variables are request.form variables
 

cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")


if dup>=1 then
 cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
 cnt=cnt & " WHERE lname='" & RF_lname & "'"
 set again=conn.Execute(cnt)
 dupl=again("Xnum")
 
 if dupl=1 then
  upd="UPDATE " & RF_course & " SET "
  upd=upd & "section" & RF_section & "='" & RF_score & "'"
  upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
  upd=upd & " AND lname='" & RF_lname & "'"
  on error resume next
  conn.Execute upd
 else
  ins="INSERT INTO " & RF_course
  ins=ins & " (lname,fname,emp_id,cname,"
  ins=ins & "section" & RF_section & ")"
  ins=ins & " VALUES "
  ins=ins & "('" & RF_lname & "',"
  ins=ins & "'" & RF_fname & "',"
  ins=ins & "'" & RF_emp_id & "',"
  ins=ins & "'" & RF_cname & "',"
  ins=ins & "'" & RF_score & "')"
  on error resume next
  conn.Execute ins
 end if
else
 ins="INSERT INTO " & RF_course
 ins=ins & " (lname,fname,emp_id,cname,"
 ins=ins & "section" & RF_section & ")"
 ins=ins & " VALUES "
 ins=ins & "('" & RF_lname & "',"
 ins=ins & "'" & RF_fname & "',"
 ins=ins & "'" & RF_emp_id & "',"
 ins=ins & "'" & RF_cname & "',"
 ins=ins & "'" & RF_score & "')"
 on error resume next
 conn.Execute ins
end if

 

 
Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it. 
 
Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Query To Count # Records In Db Based On Foreign Key
My SQL is very basic.  How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
 SELECT     COUNT(*) AS Expr1, tblArticle.ArticleIDFROM         tblArticle INNER JOIN                      tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.

View Replies !   View Related
Hiding/Showing Columns Based On The Columns Present In The Dataset
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.

 

-Thanks,

Digs

   

View Replies !   View Related
Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)
Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
    pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

 m_TableID.uName.pwszName = m_wszTableName;
 m_TableID.eKind = DBKIND_NAME;

 DBPROP rgProps[1];
 DBPROPSET PropSet[1];

 rgProps[0].dwOptions  = DBPROPOPTIONS_REQUIRED;
 rgProps[0].colid   = DB_NULLID;
 rgProps[0].vValue.vt  = VT_BSTR;
 rgProps[0].dwPropertyID  = SSPROP_FASTLOADOPTIONS;
 rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

 PropSet[0].rgProperties  = rgProps;
 PropSet[0].cProperties  = 1;
 PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

 if(m_pIOpenRowset)
 {
  if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
  {
   return FALSE;
  }
 }
 else
 {
  return FALSE;
 }

View Replies !   View Related
Acceptable Data Based On Count?
I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions

View Replies !   View Related
MDX - Select Count Based On Two Different Dimensions
I have a fact table with a create time dimension and an expiration time dimension.  I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time).  I already have these counts as measures.
 
I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above)
over the different create time periods.
 
Can someone point me in the right direction on how I would create that kind of calculated member?  What would the MDX look like?
 
Thanks for your help

View Replies !   View Related
Create Record Based On Count
I have the following data,
 
CustomerID             EngID                  EngCount
1                            A11                     2
2                            B12                     1
3                            C10                     3
 
I need to display it as,
 
CustomerID             EngID                  EngCount
1                            A11                     1
1                            A11                     1
2                            B12                     1
3                            C10                     1
3                            C10                     1
3                            C10                     1
 
Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.
 
How to do?

View Replies !   View Related
Updating Field Based On Record Count
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.


Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc


All help is greatly appreciated.

View Replies !   View Related
How To Count Cases For Different Groups Based On Different Criteria
 

Hello,

I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd,  bnb_newcases_salesd etc)

Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)

MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null

BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')








12-Jan

19-Jan

26-Jan

2-Feb

9-Feb

16-Feb



MTS





















New Cases Received

85

84

79

98

79

95



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

44

29

26

35

55

54



CreditB

6

12

9

5

7

13



CreditS

-

-

-

-

3

-



CreditP

10

11

11

24

17

7



MTS Subtotal

140

125

110

144

151

150

























BNB





















New Cases Received

12

13

14

14

6

11



Sales

-

-

-

-

-

-



SalesD

-

-

-

-

-

-



CreditR

12

11

12

10

5

9



CreditB

8

13

9

17

16

6



CreditS

-

-

2

-

-

-



CreditP

1

1

1

1

4

3



BNB Subtotal

21

25

24

28

26

19

























Total





















New Cases Received

97

97

93

112

85

106



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

56

40

38

45

60

63



CreditB

14

25

18

22

23

19



CreditS

-

-

2

-

3

-



CreditP

11

12

12

25

21

10



Grand Total

161

150

134

172

177

169

 

This is just a very brief bit of code

SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.

FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...

 

Can you please show me how to accomplish this?

Thank you in advance for your effort,

 

Rhonda

View Replies !   View Related
Count Up Based On Unique Value -- Brain Freeze
 

Ok... I know this is something that I've seen and probably done before....
 
I have a value that will show up multiple times in a table.  I want to order the table by that value and then count up from 1 to however many items that it shows up as.
 
The example below shows the results I want.  Value1 and Value2 are in the table, LineNumber is what I need to generate.
 







Value1
Value2
LineNumber

1
Hey
1

1
Can
2

1
Someone
3

1
Help 
4

1
With 
5

1
This
6

2
Very
1

2
Simple 
2

2
Issue
3

2
As
4

2

5

3
Have 
1

3

2

3
Brain
3

3
Freeze
4

 
 
--Thanks--

View Replies !   View Related
Trigger To Update Table Based On COUNT Of Column
Hello again,
 
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
 
 



Code Snippet
CREATE TABLE #Assignment
 (CallID INT IDENTITY(1500,1) PRIMARY KEY,
 AssignmentGroup VARCHAR(25),
 Assignee VARCHAR(25)
 )
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')
 
GO
 
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
 
 




What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.

 
So, the logic for the trigger would be
 
UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
 
 
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
 
Does any one have an idea or pointers as to how I should go about this?
 
Grateful for any advice, thanks
matt

View Replies !   View Related
Count Of Columns &<&> 0
How would you count the number of columns with a value not equal to 0 foreach row in a table?Thanks!Joe

View Replies !   View Related
SQL Count Columns
Hello,

I'm attempting to count the number of COLUMNS that have a specified value in a given record.

For example, suppose I have the following table:
NameDay1Day2Day3
JohnYNY
JaneYYY
JoeNNY

I'd like a querty to loop through each record and provide a total for each person for Days Present, such as the following:

Name, DaysPresent
John, 1
Jane, 3
Joe, 1

Is this possible to do in MS SQL? I'm familiar with the COUNT function but have never used it to count the columns.

Regards,
Cody

View Replies !   View Related
Count With 2 Columns?
im trying to display a category name in one column and then the number of books in that category in another column.

SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS;
Gives me an error saying something about category not being a single group function. What am I doing wrong?

View Replies !   View Related
Rows Based On Columns
I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.

I have a Table/View called [Shipment] with the data below.

ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA

I want the following as my result.

ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA

Thx.
Rav

View Replies !   View Related
Help With Count Records
Hello All,
 I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names.  I have tried this SQL statement in the SQL Express and it works very great.  
SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName
However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Can you please help me to fix this error? 
Thank you, Vic. 

View Replies !   View Related
Get Records Count
i have this function
it return 0 but the sql statement in the sql query return the right number?how is that
i want to get the number of records any other idea or fix?
Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
Conn.Open()
dr = cmd.ExecuteReader()
dr.Read()userQuota = dr("rec_count").ToString
Conn.Close()
End Function

View Replies !   View Related
Count The Records
I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row.
Example:

1|"Jane Doe"|"23 Western ave"|...
2|"Jamie Delom"|"5 East Street|...
3|"Nat Girshon"|"5678 Main Street|...
.
.

Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?

View Replies !   View Related
Count All &>0 Records
count(foundedamount>0)
does not seem work, how can I count all record and fundedamount>0?

THX

View Replies !   View Related
Count Records
Hi
i am just an beginner with sql and i am quite sure this will be a simple question
i am looking for a methode to count the number of records how can i do this?

greetz

View Replies !   View Related
Count The Columns In A Spreadsheet
 

Does anyone know how to get a count of columns in a spreadsheet before importing ?
 
Thanks in advance

View Replies !   View Related
How To Do Update Of Select Columns Based On...
the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.

View Replies !   View Related
Output Several Columns Based On Rows
I'll show my schema first, then I'll explain what I'm doing:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------


--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------


--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------


--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------


--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------


--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------



There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).

A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.

For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:

----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3

So in that example, that event had 3 sessions.

Right now, I can only output E_Title and ER_Name, I don't know how to output the session information

View Replies !   View Related
Discovering AGE Based On Date Columns
Hello.

I have three INT columns in a table that record the users birth year, month, and day.

BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)

I'd like to include a function in my query that will return their Age in years based on these three columns.



I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.


Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End

Usage (how do i pass the three columns into this function??)

SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21

View Replies !   View Related
Why Can't I Use Columns Based On Case Down In My Where Clause
 

I am writing a fairly simple sql, and I would like to write something like
 



Code Snippet
select

firstname as firstname,
case

when firstname = 'Peter' then 'yes'
else

'no'
end as whatever
 
from

MyTable
where

whatever = 'yes'
 

 
And this should then select out the rows where column number 2 is 'yes'.
 
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
 
But why?


 

View Replies !   View Related
Count Number Of Records
Using linq what would be the most efficient method of counting the number of users in the users table? 

View Replies !   View Related
How To Count Number Of Records
hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.

View Replies !   View Related
Get Records With A Count Total
Hello, I am having problems with this query below:
 1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
 

View Replies !   View Related
Select Count(*) For Getting # Of Records
Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()
 
I am getting an error that that's no data in the table.
Any suggestions?

View Replies !   View Related
How To Count The Records In A Datatable
I have the following:        Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter        Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct        Dim numRows = dF.Rows.Count        Dim dS As Data.DataTable = ResourceAdapter.CountQuery        Dim sumRows = dS.Rows.Count        DataList1.DataSource = dF        DataList1.DataBind()        LblCount.Text = numRows        LblSum.Text = sumRows    numRows is the number of records in a particular category.  CountQuery is a method that returns the total number of records in all the categories.   I don't know how to get the result of that query, the code line in bold isn't right.  How do I get this number?Diane 

View Replies !   View Related
Count Of Related Records??
Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.

View Replies !   View Related
Count All Records In All Tables
I need some help with this. I was able to count all the records in ourdatabase using the user_tables and user_tab_columns tables afterrefreshing the statistics on this database.We are doing an upgrade of a system and I will not be able to refreshthe statistics during the upgrade. I need more of a manual process ofrunning these queries.Now I do:select A.table_name, round(A.num_rows,0) as rowcount,count(b.table_name) as ColumnCountfrom dba_tables A, dba_tab_columns Bwhere A.table_name = B.table_name and A.owner in ('PS','SYSADM')group by A.table_name, A.num_rowsorder by rowcount desc, columncount descBut I can't use the num_rows anymore so I was thinking more to do this:Select A.table_name from(select count(*) from A.Table_name B where A.Table_name =B.Table_Name)from user_tableThis does not work for me since I don't know how to pass the table_namefrom the first select to the second select. The logic is there but thesyntax is not.Please help.

View Replies !   View Related
Count Records In Several Months
Hi,I've a small problem. I have a table in which one column is date. I want tocount the records for statiscs in a temptable grouped by months lets say 12months back.e.g.month 1 counts 164 rec month 2 counts 87 records and so on.I tried to solve this like this with a function SELECT COUNT(*) FROM TABLEWHERE DATEDIFF(m,Col1,GETDATE())=@counter.But I don't know how to get this thing count from 0 up to 11 to get thisthing recursive.Does anyone know how to tackel my problem? I wouls apreciate any answer.Greetz to you all

View Replies !   View Related
Count Records In A Top 10 Query
Hi

Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...

i have this so far, and it dosent give me anything...

col1 is varchar 254

SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)

ex of output

place1 100
place2 50
place3 25
...
place11 500

a query that only gives me the place11 number is enough

thx in advance //Mr

View Replies !   View Related
Count Records Between Two Dates
Hi all,

I've got a quick question.

How would I count the number of records between two dates.

I started with something like this.

SELECT COUNT(*) AS COUNT, dtAdded
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
GROUP BY dtAdded

but as you probably all know this ain't right. I would like to get just the number of records.

Thanks

View Replies !   View Related
Count Of Specific Records
I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?

Thanks!
Justine

View Replies !   View Related
Count Repeting Records
hi, i have 10 records in my column empno in my table as follows:

records are
1,5,8,9,1,7,4,8,9,5.i want to show the repeting records and how many times it is repeted ie count
please give me query for this.

View Replies !   View Related
Count Distinct Records
how can i count in sql the number or records taht would be returned if i did

select distinct site,date from allrecords

View Replies !   View Related
Count Of Records Greater Than?
Hello All,

 

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

 

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

 




Code Snippet

=Count(Fields!ResponseTime.Value > "500")

 

However, this appears to just return the count of all rows and ignores the "500" part.

 

Am I missing something? If someone could post a alternate code snippet, that would be great.

 

Thanks in advance,

Clint

View Replies !   View Related
How Do I Get A Count Of All Records Returned.
I'm trying to put the total number of records returned from from a query in the bottom of our report.  I don't want to do a count(*) in my sql stmt.

 

thanks.

 

 

View Replies !   View Related
Count Of Records Returned
How can i get a Count of the number of records returned
 
Here is my code
Im using VS2005 connected to SQL Compact v3.1
 

myCmd.CommandType = Data.CommandType.Text

myCmd.CommandText = "Select * From tblParts"
 

Dim myDataAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(myCmd)

Dim myDataSet As DataSet = New DataSet()

myDataAdapter.Fill(myDataSet)
 

TblPartsDataGrid.DataSource = myDataSet.Tables(0)

View Replies !   View Related
SQL CASE Output COUNT Into Two Columns
Hi Everyone,
 I am trying to do a query where I need to use as little C# as possible to build my gridview.  Basically I have a column called statusID.  There are about 15 options for this column but I only want to count certain ones.  I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed".  I also want it to count based on the month.  To accomplish this I have used CASE and GROUP BY.  This has worked to some extent.  Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January.  Unfortunately the query returns 2 records for the month.  The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value.  I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data.  My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL.  The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month.  Below is my query, any suggestions about how to approach this will be greatly appreciated.  If any clarification is needed please let me know.  Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
    WHEN 3 THEN COUNT(Attend.statusID)
    END AS fullUnitsUsed,CASE Attend.statusID
    WHEN 4 THEN COUNT(Attend.statusID)
    WHEN 14 THEN COUNT(Attend.statusID)
    WHEN 15 THEN COUNT(Attend.statusID)
    WHEN 16 THEN COUNT(Attend.statusID)
    WHEN 17 THEN COUNT(Attend.statusID)
    WHEN 18 THEN COUNT(Attend.statusID)
    WHEN 19 THEN COUNT(Attend.statusID)
    WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
                     INNER JOIN Property ON Attend.propertyID = Property.propertyID
                     INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
                    CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description

View Replies !   View Related
COUNT FUNCTION ON MULTIPLE COLUMNS
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.

View Replies !   View Related
Count Multiple Distinct Columns
I want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.

View Replies !   View Related
COUNT Values Across Numerous Columns
I have table where I have 5 columns that contain cancellation codes. These cancellation codesm are foreign keys that link to the 'Cancellations' table (where further information can be found on a cancellation).

What I need to do is to somehow sum up (COUNT) all the cancellation codes across that span across these 5 columns, so that I can then get a total of all types of cancellations that span across these columns.

How exactly do I do this?

The obvious code I have so far which gives the totals of the types of cancellations for 1 column is...

Code:


Code:


SELECT can.cancdesc, COUNT(patients.can4Code) AS Amount
FROM planpatients AS patients
INNER JOIN cancellations AS can
ON can.canccode = patients.can4code
GROUP BY can.cancdesc;



An example of the 5 columns with the cancellation codes is as follows...
Code:


Code:


--------------------------------------------------------
can1code - can2code - can3code - can4code - can5code
--------------------------------------------------------
01
02
03
04
05
01
02
03
04
05
...and so on...
-------------------------------------------------------



After doing the JOIN with the 'Cancellations' table, I would like my query results to generate something like...


Code:


-------------------------------------------------------
Cancellation_Type - Amount
-------------------------------------------------------
Cancelled by Patient - 10
Cancelled by Hospital- 2
etc etc
-------------------------------------------------------



I hope there is a way of doing this

Thanks

Tryst

View Replies !   View Related
Count Of Null Values In Columns
Hi,

 I would like to ask how can I get count of null values in each column together?


 I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved