Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:
Argentina 10
Brazil 5
Canada 3
I'm having trouble writing the SQL for this...any suggestions?
Thanks,
Erik
I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the followingSELECT COUNT(ID) AS iTotal, STATEFROM membersWHERE (Suspend = 0)GROUP BY STATEORDER BY STATEIn the code behind of my page I have Dim mateAdapter As New WAPTableAdapters.membersTableAdapter Dim mates As WAP.membersDataTable Dim mate As WAP.membersRow mates = mateAdapter.GetDataState For Each mate In mates Select Case mate.STATE Case "AK" LabelAK.Text = mate.ID. End Select Next What should LabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?Diane
View 7 Replies View RelatedSELECT name = SUBSTRING(name, 1, 1),
total = COUNT(SUBSTRING(name, 1, 1))
FROM products
GROUP BY SUBSTRING(name, 1, 1)
the assignment said that,
quote:"Do not display the letter and count unless at least three product names begin with the letter."
but i got the errors when I try following.
WHERE total >= 3
quote:Msg 207, Level 16, State 1, Line 4
Invalid column name 'total'.
WHERE COUNT(SUBSTRING(name, 1, 1)) >= 3
quote:Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
so what should I do?
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig
I need to figure out the number of managers without listing them. Label the column Number of Managers.
any help is appricated.
Number of Managers
-------------------------
6
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE ProjStatus
6 In Progress
3 Complete
4 On Hold
The stored procedure is as follow:
SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)
dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()
myConnection.Close()
</script>
<asp:datagrid id="dgProjSumm" runat="server"
BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
I am using Query Writer (should be SQL 2005) and have included the following code.
The end result: -should retrieve columns and display the count for two different data sources that were added by personnel in a specific department.
Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.
Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''
Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'
[Code] ....
If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.
What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?
How to count the column group values and how to print the counts on every column for column group.
I used below expression to get the total column group count.
=CountDistinct(Fields!MMU.Value, "DataSet3")
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Table_name Load_Status RecordCount User...
tbl_collection Running 1244 XYZ
I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..
With Member ProductRank as
Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]))
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]
Output:
Â
It runs for 13 seconds on my laptop. I Â have modified the query to get the results fast like With
Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])
Member ProductRank as
Rank([Date Due].[Calendar].currentmember,MySet)
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]
Output:
This query runs instantly and result is not the same. Am I missing something here?
date       time        s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date       time        s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                 3
6/9/2015 11:56:26 TestSite 900                  2
6/12/2015 12:56:26 TestSite 700                  1
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies View RelatedThe following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies View RelatedI would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
I have a table with the following structure and data
SampleTable
id name parent_id
1 Microsoft 0
2 Visual Studio 1
3 J++ 2
4 VB 2
5 C++ 2
As you can see, a record with parent_id=0 is the top level. Other levels will have a parent. I want to display the above data as below
1 Microsoft
2 Microsoft - Visual Studio
3 Microsoft - Visual Studio - J++
4 Microsoft - Visual Studio - VB
5 Microsoft - Visual Studio - C++
I tried many times with the select statement but with no luck. Do you have any ideas? Thanks,
i have a column name "Principle" and the data in this column is a dollar value. How do i write a query where when the dollar amount is <0 it displays as - and when its >0, it displays as +
View 1 Replies View RelatedHi,
I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)
However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.
Thanks.
Hi,
I need to show the value of a variable with comma seperated.
For eg: I have a variable N whose value is 123456.89
I need to display it as 1,23,456.89
How i can i display this...
Please help me
Thanks in advance
Hi,
I have the following stored procedure
CREATE PROCEDURE dbo.test(
@number varchar(128) output
)
as
select @number = * from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
return 0
Then I go in and execute it like this:
declare @number varchar(128)
exec dbo.test @number output
select @number
I get the following result:
T_PORTWARE_GBL
(Only one row returns).
However when I execute the sql statement below:
select gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
which is the same one as in the stored procedure except the variable declaration it reurns me the following:
gw_instance
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
(99028 rows of data)
Why is this hapenning?
Can anyone help me?
Thanks and Regards,
Sudip
Hi,
My table "Branch" contain a Field name as MassMail
it contains mail IDs a@yahoo.com;b@yahoo.com;c@yahoo.com;d@yahoo.com;e@yahoo.com
Now plz write me what is the SQL Statement for Retiveing MassMail
except one or Two Mail ID
Example : Retrive
a@yahoo.com;b@yahoo.com;d@yahoo.com;e@yahoo.com
Here c@yahoo.com is not Include
Thanks
Thanks
Hi,
I have the following stored procedure
CREATE PROCEDURE dbo.test(
@number varchar(128) output
)
as
select @number = gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
return 0
Then I go in and execute it like this:
declare @number varchar(128)
exec dbo.test @number output
select @number
I get the following result:
T_PORTWARE_GBL
(Only one row returns).
However when I execute the sql statement below:
select gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
which is the same one as in the stored procedure except the variable declaration it reurns me the following:
gw_instance
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
(99028 rows of data)
Why is this hapenning?
Can anyone help me?
Thanks and Regards,
Sudip
Hai, My condition of the program is to check whether the username is available or not. If it is available, I should insert that username into the db, if not I should display "Username already exists". I've written SP for the condition, but I dunno how to display the error message in asp.net. I've my codings below: DB - SP:alter procedure insert_user@username varchar(20)asif exists(select username from test where username=@username)print 'Username already exists'elseinsert into test(username) values(@username) Default.aspx.cs: protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection insertconn = new SqlConnection("Server=achuthakrishnan;Initial Catalog=classifieds;Integrated Security=SSPI");
SqlCommand insertcmd = new SqlCommand("insert_user", insertconn);
insertconn.Open();
insertcmd.CommandType = CommandType.StoredProcedure;
insertcmd.Parameters.AddWithValue("username",TextBox1.Text);
SqlDataReader dr;
dr=cmd.ExecuteReader();
insertconn.close();
} What is the condition I should check after SqlDataReader dr? Or else, I believe I should use @@error to display error messages in asp.net. I googled a lot and couldn't find the exact solution. Can any one help me?
I am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following:
A B C D
A E F G
B H I J
I want this to look like -
A B C D E F G
B H I J
I want one row, but not one column, I need to maintain each value in its own column, withinin a single row.
Doesn't matter to me if that transition happens in my View or as the outcome of my Join.
I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one.
This is my VIEW stmt -
SELECT [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM dbo.pkgapproversWHERE ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E')
This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR]
FROM [PkgAction]
FULL JOIN vw_PkgApprovals
ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID]
WHERE 1=1'
Any thoughts....Thanks
I'm trying to follow along with lesson07 Web Development Express series and when I drag the SqlDataSource tool onto my form I do not see the object on the screen. I therefore can not configure it until I have placed GridView object onto my form. I hope you can help me in how to view the SqlDataSource or inform me where I can get further information
View 2 Replies View RelatedI want to publish Images in an asp-web-page. How can I do that?
View 1 Replies View Related