Query Doesnt Count Correctly

Jun 17, 2008

Hello,

Im having a problem with this query....SELECT a.ref, COUNT(b.entrypage) AS counter
FROM SiteCore.dbo.Campaigns AS a LEFT OUTER JOIN
webStats AS b ON b.entrypage LIKE '%?ref=partners%'
GROUP BY a.ref

 But i want it to look more like this, so it pulls the approprite rows its selfSELECT a.ref, COUNT(b.entrypage) AS counter
FROM SiteCore.dbo.Campaigns AS a LEFT OUTER JOIN
webStats AS b ON b.entrypage LIKE '%?ref=' + a.ref + '%'
GROUP BY a.ref

 The first one return the correct count where as the latter doesnt retuen anything, could any one shed any light as to what the problem could be?

 Thanks

Bart

View 10 Replies


ADVERTISEMENT

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007



I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'


As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

Why Doesnt My Query Work

Dec 6, 2007

select distinct
a.Patients,
b.Patients,
a.pct,
b.pct

from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null

(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct

View 6 Replies View Related

Simple Query Doesnt Work

Apr 23, 2008

I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???

would it be a problem with the sdf file? help please!!!

View 3 Replies View Related

Linked Server Distributed Query Doesnt Work

Jun 21, 2006

 

Hi,

 The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.

 

OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

View 7 Replies View Related

Query To Only Display Information From One Table Where The Foreign Key Doesnt Exist In The Other Table.

Nov 28, 2006

I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here. 

View 1 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

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

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

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

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

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

I Need Help With A Query And Count

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

Sql Count Query

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

How To Query COUNT

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

Query Row Count

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

SQL Count On A Sub Query

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

How To Use Count In Mdx Query

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

Count Query

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

Query With COUNT

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

COUNT And SUM In Same Query?

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

Hi Everybody - COUNT QUERY PL

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

How To Get Row Count From An Inner Query

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

Count Query

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

Count Query

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

Count Query

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

QUERY WITH COUNT

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

How To Count Rows In This Query...

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

How To Do 3 Count On The Same Table In One Query?

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

How To Get A Count.....complex Query

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

Bottom N/Count (*) Query

Jun 30, 2005

Hi,

I am trying to construct a query which wuill give me the following results:

Source Data:
ID Version
s015 0
s015 1
s016 0
s017 0
s017 1
s017 2

Results:
ID Version
s015 1
s016 0
s017 1
s017 2

So, I was to discard the Version 0s unless there is no other version for the ID. Can anyone help? Thanks.

excelthoughts

View 1 Replies View Related

Help With Count And Group By Query Pls!!

Dec 6, 2006

ok, so i have this table "SOLD_PRODUCTS" with this columns:
idProduct (int), quantitySold (int)

so i want to know how many products i have sold.

i have this query

Code:

SELECT idProduct, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


and get this
74 5
75 2
79 1
etc etc etc

this works correctly, but it doesnt counts the quantity sold, so i changed the query to


Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


but it crashes, mssql says i must add quantitySold to the group by clause.

but if i put the query

Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct,quantitySold



i dont get the expected data, the idProducts appear more than once, they are no longer grouped by, the results in the count(idProduct) are wrong (i am not sure what they are counting now)

so now i dont understand how i can make my query, all i want is a query where in one collumn i have the product id, and in the other the quantity sold (that would be the count of all the product ids found multiplied by the quantity sold)

so now i am completelly lost, now i dont know what to do with my query.

any help pls pls pls!!!!

View 1 Replies View Related

Query To Count Holidays

Aug 9, 2004

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.

View 2 Replies View Related

Count Records In A Top 10 Query

Dec 5, 2005

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 1 Replies View Related

Minute Count Query

Aug 22, 2007

I need a query that gives me the sum of every rows (time column) with lower 'rownr'

the result:
rownrtimetimesum
1100
21010
31020
41030
51040
61050
71060
81070

current table looks like this:
rownrtime
110
210
310
410
510
610
710
810

and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime

thx for all help

//Mr

View 14 Replies View Related

Distinct Count Query

Dec 11, 2007

I have a table with following fields
tdate
custcode
prodcode


table is filled with full year data and i want following result

I want count of distinct custcode in every past three months.

for example
Result like this

month tjan tfeb tmar tapr tmay ..... tdec

prod1
prod2
.
.
prod5

And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required.
Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.

Can any1 help me please.

I am using MS SQL 2005 and above table is a big table (approx 10 million records)

Sham

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved