I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:
The following query:
SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
Returns this result:
Category
BMS
BMS
Water
BMS
BMS
Air
And the following query:
SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID
Returns this result:
AssetQty
4
1
1
I need to have both of those results returned, as a single result. Such as:
Category AssetQty
BMS 4
WATER 1
AIR 1
However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:
Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS ( SELECT PQIM.PQPageID ,PQIM.PageURL as PageDescription ,CONVERT(Date,NCPI.RequestDateTime) AS [Date] ,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR] ,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
How can I tell if table "CompanyInfo" has any rows? Below is the partial code behind and the SP I am running to fill CompanyInfo; PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM tblStoreSubmitWHERE C_ID = @CompanyIDRETURN SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo");
I'm trying to do a sum of a count. I'm running the below query. I want a sum of BrowserCount however I want to return the datatable which is returned by this query. Is that possible should I be using a return value? Or is there another way?
Select UA.Browser_ID, B.Browser_Name_NM, count(B.Browser_Name_NM) as BrowserCount From llc.User_Agent_TB as UA Left Join llc.Browser_TB as B on UA.Browser_ID = B.Browser_ID Group by B.Browser_Name_NM, UA.Browser_ID Order by BrowserCount DESC
This seems pretty straightforward, but I can't seem to figure out how to do this.
So I'm looking at the last fifteen days of data and I want to see how many data points are below a certain value, and the average of those data points. This is the query I'm currently using:
Code Snippet SELECT COUNT(*) Below, TRUNC(t.THETIME) Day, ROUND(AVG(t.THEVALUE),2) ValAvg FROM MyTable t WHERE t.THEVALUE <= 50 AND t.THETIME >= TRUNC(SYSDATE-14) GROUP BY TRUNC(t.THETIME) ORDER BY TRUNC(t.THETIME)
I later have to compare the result of this query to the same query without the 't.THEVALUE <= 50' condition. The problem I'm having is that if there are no values below 50 there is no '0' with that date. Is there a way to return 0's for days with no values below 50 that meet the time constraints? Thanks.
Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.
Table Def: tblOfficePivot ----------------------- key <- PK ID officeLink <- Link to office record employeeLink <- link to employee record.
I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)
I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:
SELECT COUNT(officeLink) AS theTotal FROM tblOfficePivot GROUP BY officeLink HAVING COUNT(*) BETWEEN 1 AND 250
The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:
theTotal -------- 1. 50 2. 123
What I want instead is the total number of offices, in this case 2.
Is there a way to do this without the COMPUTE clause?
Thanks for any suggestions, this one is driving me crazy.
The following query returns 2142 rows which is correct.
Code: select COUNT(*), sum(v.currentMarket) from TRMaster m inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A' where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1 group by m.Year, m.Parcel having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
Having a little trouble with this sp... just need to return the count.....CREATE PROCEDURE dbo.getTotalObjectives @courseId VARCHAR(20) ASBEGIN DECLARE @errCode INT SELECT count(*) FROM objstructure WHERE courseId = @courseId SET @errCode = 0 RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation FROM cmiDataModel WHERE studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.
Total Units = 7 - easy to do by using count() Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _ & " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _ & " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _ & " Order By DateClosed DESC ")
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
select count (*) from MEMBERS,dbo.MEMBER_PROFILE where MEMBER_PROFILE.member_no = members.member_no AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59' and email <> 'selfbuy_customer@cafepress.com' and ROOT_FOLDER_NO is not null and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager] -- Add the parameters for the stored procedure here @StartTicks bigint, @EndTicks bigint, @TimeDiff decimal(9,2), @NumRows int OUTPUT
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Hi! I'am new to this forum and would apreciate any feedback on my problem. I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299. The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week, ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average FROM SERGEL_PREPAID INNER JOIN TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD') GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1 GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help Best regards Gard S
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name) From msdb.dbo.backupset where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN (SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR (user2_guid = @userGuid))) AND (UserId IN (SELECT UserId FROM espace_ProfileProperty))
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red  - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red  - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green   pen       2    0    1   marker    0    1    0   pencil      1    3    0   highlighter  1    1    1Â
We tried Sum but not able to display in single row.
select * from HistoryLog I will got a return result with Mainindex �Sales�Date 3 kinds of columns Data , If I need to add one column by myself let the result like below Mainindex �Sales�Date�Total the Total's value is from count(MainIndex) of HistoryLogDetail Table , result will look like below MainIndex Sales Date Total1 John Jan-05-1999 2 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 2)2 John Jan-07-1999 1 →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 1)3 Liz Jan-08-1999 14 Yumi Jan-08-1999 0 I trid wirte a code to got the total column but what I got is like thisMainIndex Sales Date Total 1 John Jan-05-1999 4 2 John Jan-07-1999 43 Liz Jan-08-1999 4 4 Yumi Jan-08-1999 4 what the columns of Total shows is .. how many record are in HistoryLogDetail Table but that's not I need ..can you help?thank you very much
I am new to SQL so please bear with me. I was wondering how to create a new column that counts the EntityId COUNT(CdsQuotes.EntityId) and puts it into a seperate column.
What I am trying to achieve is a count of entity IDs from one table and a count of entity IDs from another table - but the 2nd count must be from the first counts results (hope this makes sense).
So it returns a count from table 1 and then a corresponding count from table 2 (based on table 1 entityIDs)
Any ideas?
SELECT
Entity.EntityName, SuspectBond.EntityId, COUNT(SuspectBond.EntityId) as "Suspect Bonds"
FROM SuspectBond
INNER JOIN Entity ON SuspectBond.EntityID = Entity.EntityId
Hi thereI have the following two tablesmainprofile (profile varchar(20), description)accprofile (profile varchar(20), acct_type int)Sample data could bemainprofile----------------prof1 | profile oneprof2 | profile twoprof3 | profile threeaccprofile--------------prof1 | 0prof1 | 1prof1 | 2prof2 | 0Now doing a join between these two tables would return multiple rows,but I would like to know whether it would be possible to returnacct_type horizontally in a column of the result set, e.g.prof1 | profile one | [0,1,2]prof2 | profile two | [0]I could probably manage this with cursors, but it would be veryresource intensive. Is there a better way?Regards,Louis
Hello, I'm using C# to access sql server. When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use? Thanks.
SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount FROM tbl_CYProcessedSales WHERE tc NOT LIKE 'T%' AND tc NOT LIKE 'R%' AND ISNUMERIC(TC) = 0 AND NOT submitrep1 = '' AND Submitrep1 = 'along' GROUP BY submitrep1, tc
Returns a result like this:
ALONG KL 65 ALONG KP 35
How can I return the one record that contains the MAX(TCCount)?
