Basic COUNT Query
Aug 7, 2013
I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.
This didn't work for me:
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows
View 5 Replies
ADVERTISEMENT
Jul 14, 2007
Suppose we get two following tables:
Table A: [USER_ID] [varchar] (11) NOT NULL , [COURSE_ID] [varchar] (11) NOT NULL ,
Table B:
[COURSE_ID] [varchar] (11) NOT NULL , [COURSE_NAME] [varchar] (50) NOT NULL , [COURSE_NO] [varchar] (15) NULL , [BEGIN_DATE] [datetime] NULL , [END_DATE] [datetime] NULL , [CREATER] [varchar] (11) NOT NULL ,
and during the execution of my program, I can get the current use's id (USER_ID), say U0001.
How can I retrieve the result set containing [COURSE_NAME], [COURSE_ID], but the current user's id (U0001) have Not been assigned in Table A.
Thanks in advance.
Ricky.
View 3 Replies
View Related
Jul 14, 2001
I'm not getting it. The query below "e.g." is exactly as I think it should be except for the <Join Clause> as there is only the match on site name. I tried joing on the site name, but only got the sites in the log. I want all the site names even if they don't have a log item for a given date range.
I'd post my real query if it would help, it's just that I'm chopping up some strings and converting some time stamps to dates as varchar for readability for the final report, which is an MSExcel pivot table.
My original post lies below.
TIA
jEfFp...
Reply...
Date: Basic Query Help (reply)
RickD (Rick.Davis@Schroders.com)
7/11/01 6:41:26 AM
Make log an outer join to site.
e.g
select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
You didn't seem to have any logical join between the tables, but i'm sure you have, just put this in the <Join Clause>.
Oh, and read BoL, it helps no end.....
------------
Original Post...
Jeff Proctor at 7/10/01 11:18:31 PM
my 7 day query....
select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.
I have 2 tables...
table1 sites
name
smithj
anandt
burtot
proctj
etc....
table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....
View 1 Replies
View Related
Nov 23, 2005
ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan
View 6 Replies
View Related
Jul 20, 2005
Ok, I'm a beginner so forgive me for my ignorance. Could someone helpme with this?tbl_x has two fields xid and xlistxlist being a list of numbers.....1, 5, 6, 8i want to create a new table from tbl_x that converts each number inthat xlist into a row in tbl_newso for exampletbl_x has 3 rowsxid xlist1 1, 4, 52 2, 3, 73 2, 1, 7i need a query or sql script that will convert that table with a listinto the following....tbl_newid xid xlid1 1 12 1 43 1 54 2 25 2 36 2 77 3 28 3 19 3 7
View 2 Replies
View Related
Nov 20, 2007
Hi I am having two very basic problems which are very frustrating.
I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.
This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:
string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg";
SqlCeResultSet resultSet = null;
SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);
sqlCommand.Parameters.Add(SevParam);
sqlCommand.Parameters.Add(MsgParam);
try
{
pConn.Open();
resultSet = sqlCommand.ExecuteResultSet(ResultSetOptions.None);
}
finally
{
pConn.Close();
}
return !(resultSet == null);
resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.
Thanks!
=Spencer Whitman
View 1 Replies
View Related
Apr 28, 2006
Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.
I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.
What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.
Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,
Regards
View 1 Replies
View Related
Aug 21, 2007
The 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
View 1 Replies
View Related
Jan 28, 2005
How would I convert this query to ANSI 92 standard?
select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)
View 1 Replies
View Related
Apr 29, 2008
hi
i am learning SQL basic commands ( like , where, having, group by, order by , join ,and simple sub-query) and I got one exersice I cannot solve
table RECIPE has 3 columns , ( pizza ,ingredient,amount)
each pizza could use 1 / many ingredient, each ingredient could be used by 0 / 1/ many pizza, amount means the quantity of that ingredient used in certain pizza
question:
(List the ingredients, and for each ingredient, also list the pizza that contains the largest amount of this ingredient.)
============================
Raw data :
recipe
======
pizza ingredient amount
--------------------------------
margarita spice 5
margarita cheese 120
ham ham 150
ham spice 5
napolitana anchovies 100
napolitana olives 75
napolitana spice 10
hawaiian ham 100
hawaiian pineapple 100
hawaiian spice 5
cabanossi cabanossi 150
cabanossi spice 10
siciliano onion 50
siciliano capsicum 75
siciliano olives 50
siciliano anchovies 50
siciliano spice 15
americano salami 120
americano pepperoni 75
americano spice 10
mexicano onion 75
mexicano capsicum 75
mexicano mushroom 50
mexicano chilli 25
mexicano spice 20
seafood seafood 200
seafood spice 5
garlic garlic 25
garlic spice 10
vegetarian onion 50
vegetarian capsicum 50
vegetarian mushroom 50
vegetarian peas 50
vegetarian tomato 50
vegetarian spice 5
mushroom mushroom 100
mushroom spice 5
special cheese 25
special tomato 25
special ham 25
special anchovies 25
special olives 25
special mushroom 25
special bacon 25
special egg 25
special pineapple 25
special cabanossi 25
special salami 25
special capsicum 25
special onion 25
special peas 25
special seafood 25
special spice 10
stagiony ham 75
stagiony mushroom 50
stagiony olives 50
stagiony anchovies 25
stagiony spice 10
====================================================
Query Results needed:
ingredient pizza amount
------------------------------
anchovies napolitana 100
bacon special 25
cabanossi cabanossi 150
capsicum mexicano 75
capsicum siciliano 75
cheese margarita 120
chilli mexicano 25
egg special 25
garlic garlic 25
ham ham 150
mushroom mushroom 100
olives napolitana 75
onion mexicano 75
peas vegetarian 50
pepperoni americano 75
pineapple hawaiian 100
salami americano 120
seafood seafood 200
spice mexicano 20
tomato vegetarian 50
=============================================
Thanks
View 10 Replies
View Related
Aug 19, 2005
Hi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
View 9 Replies
View Related
Apr 19, 2004
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESC
View 2 Replies
View Related
Jun 13, 2006
In the tutorial Creating a Basic Package Using a Wizard > Lesson 1: Creating the Basic Package >
says to use the following sql statement on the query page:
SELECT * FROM [Customers$] WHERE NumberCarsOwned > 0
When I paste the query in I get the message :
This SQL statement is not a query.
Does anyone have any suggestions? The input and output are set up correctly and I have the sample excel file Customers.xls.
I am new to all this, is there some setting I need to change for the tutorial to work or..?
FYI I have installed Sql 2005, Sp1.
View 4 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Aug 6, 2006
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
View 5 Replies
View Related
Feb 26, 2007
I have a pretty simple query:
Select title, avg(score) from votes group by title order by avg(score) Desc
This returns a result like this:
Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0
The values in the score field are always 1, 2, 3, 4, or 5
What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this.
Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2
I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.
Any suggestions would be greatly appreciated,
Chris
View 1 Replies
View Related
Jun 2, 2008
I am trying to count a column in my tbl. I have a table with the following, What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent = 1. So this individual should have 2 days of attendance. I wrote some code belwo but it does not seem to count correctly, some it does and some it does not.
intAssignedPersonnelID intUICID strSSN dtAttendance bitPresent intDrillStatus intMileage bitDatePayed210 1 333333333 5/26/2008 1 2 210 1 333333333 5/27/2008 1 2 210 1 333333333 5/28/2008 0 2
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _
& "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _
& "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _
& "from cms.dbo.tblSIDPERS as s INNER JOIN " _
& "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
View 2 Replies
View Related
May 22, 2004
If I want to find out how many items of a field is in a table like Type1, Type2... to build a table like
Type1, 10
Type2, 25
...
Is is better to open the table -> Select Type From Table
and then go down each row and record the count of a type or is it better to
Select Distinct Type from table to get the types and then do each type's select to get the count
Select Count (1) from table where type = 'Type?'
Thanks,
Frank
View 1 Replies
View Related
Nov 3, 2005
I tried searching, but no luck.
I am trying to return a row count of a query...
I have a table that is grouped by a date. Some records have the
same date. I am just trying to return the total row count of the
query.
Here is the sql:
SELECT ActiveWeek
FROM wcWARS
WHERE CreatedBy = 9034 AND FlagComplete = 1
GROUP BY ActiveWeek
I just want to return the total grouped column.... I thought Oracle had
a ROWCOUNT function that returned the total rows of the query.
I was unable to find one for SQL Server.
**Note: I already tried @@ROWCOUNT ~ This returns the total rows of the table.
Thanks
Larry
View 4 Replies
View Related
Dec 20, 2005
Hello All,
I have two tables A and B.
A and B have column x in common. The relationship between A and B is one to many.
I would like to count the number number of x's in table B and group by the x and then join this result back to table a.
My attempt :
SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id
Anyone with any ideas.
View 1 Replies
View Related
Nov 9, 2006
hello people... sorry im new with this stuff, cant get it right... i need help...
suppose i have a table that has the stores in columns and and order count as a row:
Store1 Store2 Store3 Store4
ordercount 100 null null 30
assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...
View 3 Replies
View Related
May 26, 2008
Hi
I have two tables, Sales Headers and SalesLines. The SalesHeaders table will hold basic details of a sale, and the Sales Lines table will hold details of the items in the sale.
An example of tables are as follows:
SalesHeaders:
Sales_Ref, OrderDate, Customer_No
111,01/05,2008,301
112,01/05/2008,333
113,01/05/2008,309
114,03/05/2008,306
115,03/05/2008,309
...
SalesLines:
Sales_Ref, PartNumber, Qty
111,e4334,3
111,d434,1
111,r555,20
112,r332,1
113,f442,5
114,d332,2
114,s324,7
114,s432,5
114,l567,2
115,p098,10
What I am trying to achieve is finding a count of SaleHeaders where it has only 1 item in the SalesLines table. ( I hope this makes sense)
I tried using the follwoing query:
SELECT
sh.[Sales_Ref]
,COUNT(sl.[Sales_Ref])
FROM dbo.SalesLines AS sl
LEFT JOIN dbo.SalesHeaders AS sh ON sl.[Sales_Ref] = sh.[Sales_Ref]
GROUP BY
sh.[Sales_Ref]
HAVING COUNT(sl.[Sales_Ref) = 1
But this returned all the record with a count of 1. Is it possible to just return the number?
Thanking you in Advance!
View 3 Replies
View Related
Jun 12, 2008
I have 3 tables with 1 to many relationship as discribed below
Table A
CompanyId CompanyName
1 ABC
2 BCD
3 XYZ
4 TTT
Table B
CompanyId LocationId
1 a
1 b
2 c
3 d
Table C
LocationId DepartmentId
a it
a hr
a el
b gh
c tr
I want to write a query scan the Table A to display row in table A and # of Location and Department belong to the Company, like this:
CompanyIdCompanyName# of Location# of Department
1ABC24
2BCD11
3XYZ10
4TTT00
Please help me
Thanks
View 4 Replies
View Related
Apr 16, 2014
This query gives me part of what I want:
SELECT
TS_EXEC_STATUS as 'Status',
COUNT(*) as 'Total',
FROM TEST
WHERE TS_EXEC_STATUS <> ''
GROUP BY TS_EXEC_STATUS
ORDER BY TS_EXEC_STATUS
This query returns this:
Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106
But I would like two more lines so the results are like this:
Status Total
Failed 3
No Run 132
Not Completed 6
Passed 106
Total 247
Percent Passed 42.9
I don't know how to get the last two lines with the Total of 247 and the Percent Passed of 42.9 (106/247).
View 4 Replies
View Related
Nov 12, 2005
I have a query as below:
---
select distinct
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.",
csd1.csd_labl + ' ' as "Description",
csd1.csd_issu as "Docu Dt",
csd1.csd_altr as "Alternate Number",
csd2.csd_altr as "Transmittal Reference No.",
csd2.csd_issu as "TIssu Dt",
trd1.trd_recd as "TRecd Dt",
apr1.apr_reqd as "RReqd Dt",
case when apr1.apr_stat is null then 'Unknown / Pending / No Reply' else apc1.apc_libe end as "Document Status",
csd3.csd_altr as "Reply Reference No.",
csd3.csd_issu as "RIssu Dt",
trd2.trd_recd as "RRecd Dt"
from
E0437csd csd1,
E0437tra tra1,
E0437csd csd2,
E0437trd trd1,
E0437apr apr1,
E0437apc apc1,
E0437tra tra2,
E0437csd csd3,
E0437trd trd2
where
csd1.csd_orig = 'BS' and
csd1.csd_subj like '%WN1%' and
csd1.csd_type = 'D' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'PARSO' and
tra1.tra_type = 'A' and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_cc = '0' and
trd1.trd_part = 'PARSO' and
apr1.apr_docu = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
(apr1.apr_stat = apc1.apc_code or apr1.apr_stat = '') and
tra2.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra2.tra_part = csd1.csd_ownr and
csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi = tra2.tra_trno and
trd2.trd_trno = tra2.tra_trno
order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi
---
I need to count the resulting records of the query.
How do i have to do this, could you please tell me ...
I am new to SQl and I tried but could not solve it ...
Thank You :)
Jari
Computer Engg
View 3 Replies
View Related
Sep 27, 2007
Hi All,
I have the following SQL query:
select temp.emp_id, temp.rownum
from
(
select emp_id, row_number() over (order by emp_id) as rownum from employee
) temp
where temp.rownum <=10
group by temp.emp_id
I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.
View 4 Replies
View Related
Apr 5, 2006
hi! I am new to this whole SQL language...
had a question about using a count function or if i shuld even be using a count function for this...
Data I have to work
with
I would like my result set to look like this:
ID ID2
ID ID1Count ID2Count ID3Count ID4Count
A
1
A
2
2
2
3
A
1
B
1
0
3
1
A 2
A 3
A 2
A 4
A 4
A 4
A 3
B 3
B 4
B 1
B 3
B 3
I am stumped.... ANy help would be great... thanks!
nick
View 3 Replies
View Related
Sep 14, 2007
Hi
I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had
I need to count the no of students for the different Intervention field which are like 14 different types
against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below
A student can be Male and Hispanic Type 1
also
Male Hispanic Type 2
Male Hispanic Type 3
So his count is made in three places
I need to do this
for the whole District level
then for each Center under District level
then for each school under Center level
DISTRICT SUMMARY
Intervention
Total
Male
Female
Asian
Black
Hispanic
Indian
Multiracial
White
TYPE : 1
7
3
4
4
2
0
0
0
1
TYPE :2
6
3
3
1
0
4
0
0
1
TYPE : 3
3
1
2
1
2
0
0
0
0
TYPE : 4
5
2
3
2
3
0
0
0
0
TYPE : 5
1
0
1
1
0
0
0
0
0
TYPE : 7
2
1
1
0
2
0
0
0
0
TYPE : 8
1
1
0
0
0
1
0
0
0
TYPE : 9
2
1
1
0
1
0
0
0
1
TYPE : 14
1
1
0
0
1
0
0
0
0
Please help
Thanks
View 5 Replies
View Related
Mar 24, 2008
I need to get a query result from a Derived Table that I have. Here is an example of the data from my Derived Table, based on AdvID. The following is a result of students that are in relation with the advisor.
studentPIDM AdvPIDM AdvID AdvFirstName AdvLastName Sprhold StvHlDesc
123456 14000 N12345678 John Smith 01 Letter Sent
123456 14000 N12345678 John Smith E1 Library
654321 14000 N12345678 John Smith NULL NULL
134567 14000 N12345678 John Smith 01 Letter Sent
134567 14000 N12345678 John Smith AM Admin Hold
134567 14000 N12345678 John Smith E1 Library
155544 14000 N12345678 John Smith NULL NULL
233555 14000 N12345678 John Smith NULL NULL
What I want, is to get the count of students that have holds, and the count of students that do not have a hold. From the data above, the students that do not have a hold have a NULL value on sprhold column. Also from this data, you can see that some students have multiple holds, but they should be counted as only once when the Count is performed. The desired result would look like the following:
AdvPIDM
AdvID
AdvFirstName
AdvLastName
StudentsWHolds
StudentsNoHolds
14000
N12345678
John
Smith
2
3
Some Sample Data
Code Snippet
Declare @t Table
(
StudentPIDM INT,
AdvPIDM INT,
AdvID nvarchar(15),
AdvFirstName nvarchar(15),
AdvLastName nvarchar(25),
sprhold nvarchar(3),
stvhldesc nvarchar(50)
)
Insert INTO @t Values (123456, 14000, 'N12345678', 'John', 'Smith', '01', 'Letter')
Insert INTO @t Values (123456, 14000, 'N12345678', 'John', 'Smith', 'E1', 'Library')
Insert INTO @t Values (654321, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', '01', 'Letter')
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', 'AM', 'Admin Hold')
Insert INTO @t Values (134567, 14000, 'N12345678', 'John', 'Smith', 'E1', 'Library')
Insert INTO @t Values (155544, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Insert INTO @t Values (233555, 14000, 'N12345678', 'John', 'Smith', NULL, NULL)
Select * from @t
View 6 Replies
View Related
Aug 14, 2007
I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below
I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4
STATUS
* 0 1 2 3 4
+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)
region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)
-------region 8
+school count(id) count(id) count(id) count(id) count(id) cnt(id)
The fields are in the same table
employee ID region status
A 1 1
B 1 0
C 2 3
Please help
THANKS
View 5 Replies
View Related
Dec 20, 2007
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
View 8 Replies
View Related
Mar 13, 2006
HelloI have this stored procedure:SELECT @openissue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND DateDIFF( day, TOpenIssue .DateStart, GETDATE() ) >= 0SELECT @oiclosed=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code = 'CLOSED' and Project=@project SELECT @oipastdue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND TOpenIssue .DateEnd<getdate()Is there away to optimize it in only one select statement?Thanks
View 2 Replies
View Related
May 26, 2004
Hi all,
I have a problem, i have table which has a effective date, termination date and client, I have to have a count for active people month by month from july 2003 till may 2004. the date fields are in datetime format, how would i do that?
Thanx in advance!
View 8 Replies
View Related
