Please Help With The SQL COUNT Statement!

Oct 25, 2004

I'm trying to run this SQL statement in my ASP code, sql="SELECT COUNT(*) FROM order_list WHERE ref_index='"&ref_index&"'"

The problem is, should i create a variable to store the returned integer? I tried rs_itemcount = conn.execute(sql) and then response.write(rs_itemcount.value)

But it returns with an error... any ideas how to store the integer and use it?!

thx!

View 3 Replies


ADVERTISEMENT

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

SQL COUNT Statement

Jul 15, 2004

I am trying to count records in SQL Server. I have this Stored procedure but I am getting SQL errors.


Alter Procedure usp_rptQualityReport3 As

SELECT * FROM viewQualityReport

SELECT COUNT([FailureReason]) AS FC

WHERE
(((viewQualityReport.FailureReason) <> N'NONE'))

ORDER BY
FC


I am trying to count records that have like FailureReasons. I am selecting all the records from the view I created and then trying to count the records in the second Select statement. Basically what I want to do is counf them so I can then rank them starting with failure reasons that happen the most. I don't know what I am doing wrong.

View 1 Replies View Related

Where Statement Again A Count

Jan 14, 2008

I can insert into a temp table and get this to work fine but it am unable to pass my sdate and endate parameter from reporting services.

This Works Fine I Just want to throw in a where statement. How do you get this to work? I tried [] () ''

WHERE Cola=Count(*) = Colb=Count(DidNotAttend)


Use Consumer

Select TripsId.ID#, Cola=Count(*), Colb=Count(DidNotAttend)

FROM Trips INNER JOIN

TripsId ON Trips.Rec# = TripsId.TripId#

Group by TripsId.ID#

View 3 Replies View Related

Trying To Get A Count In A Select Statement

Oct 21, 2007

When I try and execute this query I get the belwo error.
I want to get the ItemName and the Count as one column.
How can this be done? 
SELECT itemName, itemName +' - '+ COUNT(itemName) AS itemNameCount FROM tblItems GROUP BY itemName
ERROR: Conversion failed when converting the nvarchar value 'Spark Plug - ' to data type int.

View 3 Replies View Related

Using A Count If Within A Group By SQL Statement?

May 21, 2008

I have the following SQL Statement:
SELECT     CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM         tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL

View 1 Replies View Related

Increment A Count I Sql Statement

Jan 9, 2004

How can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get

miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3

View 5 Replies View Related

Count In Case Statement

Apr 24, 2014

I have to count the number of Ideas and Markets here.

CASE WHEN Team IN ('Development/Deployment Project', 'Deployment Fixed Team', 'Development Fixed Team', 'Non Fixed Team') THEN 'Ideas' ELSE 'Markets' END

View 4 Replies View Related

Using A Count W/in A Sub Select Statement

Mar 2, 2006

I'm trying to create a DTS package that uses CDO to send users an email. I need to create a sql query that counts two columns. I also need to create aliases for these two columns and then reference this in the sendEmail function. I have something that looks like this but I'm getting a DTS error. I think that it's because I'm not using an alias to reference Valid and Invalid. Can someone tell me how to alias the subselect columns correctly?? thanks :)

select advertiseremail, accountnumber from miamiherald where AdvertiserEmail is not null (select Valid = (select count (*) from miamiherald where validad = 1), Invalid = (select count (*) as Invalid from miamiherald where validad = 0))

View 1 Replies View Related

Help With COUNT In SELECT Statement

Jul 20, 2005

Could someone assist with getting the count function working correctlyin this example please. I know the count function will return all rowsthat do not have null values, but in this case I want to count all therows except those with a zero sale price, (which are unsold).The table shows works offered for sale by an artist, with a positivefigure under SalePrice indicating a sale, and I want to count thenumber sold by each auction house, and sum the sale price by auctionhouse. The table is as follows:NameSalePriceAuctionDowling12000ChristiesDowling 0ChristiesDowling10000ChristiesDowling 0ChristiesDowling 0ChristiesDowling 6000SothebysDowling 0SothebysDowling 0SothebysDowling 8000SothebysDowling 0SothebysDowling 0SothebysDowling 0SothebysWhen I run this query:SELECT MyTable.Name, Count(MyTable.Name) AS [Number],Sum(MyTable.SalePrice) AS TotalSales, MyTable.AuctionFROM MyTableGROUP BY MyTable.Name, MyTable.AuctionHAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));The results are:NameNumberTotalSalesAuctionDowling 5 22000 ChristiesDowling 7 14000 SothebysThe TotalSales is correct, but the Number (Count) is incorrect, as therows with zero were also included. The results should be:NameNumberTotalSalesAuctionDowling 2 22000 ChristiesDowling 2 14000 SothebysHow do I prevent the unsolds (zeros) being counted?Thanks in advance,John Furphy

View 2 Replies View Related

Trying To Count A Case Statement?

Dec 3, 2007

I need to get a total count of leads and then separate the counts by either Retail or Wholesale -


Here's my table schema -

CREATE TABLE [dbo].[Sent] (
[IdentID] [int] IDENTITY (1, 1) NOT NULL ,
[LeadID] [bigint] NOT NULL ,
[AffiliateID] [bigint] NULL ,
[PartnerID] [int] NULL ,
[FranchiseID] [bigint] NULL ,
[FirstName] [t_Name] NULL ,
[LastName] [t_LastName] NULL ,
[Address] [t_Address] NULL ,
[Zip] [t_ZipCode] NULL ,
[Make] [t_Make] NULL ,
[Model] [t_Model] NULL ,
[DateIn] [datetime] NULL ,

Here's my query - Since I'm grouping by the partnerid


select distinct make, count(leadid) as TotalCount, case
when PartnerID = 1 then 'retail'
else 'wholesale' end
as disposition
from leads_sent (nolock)where datein between '2007-09-01' and '2007-09-30'
group by make, partnerid
order by make

Here's a sample my current output -

Acura
1 wholesale Acura
2 wholesale Acura
4 wholesale Acura
5 wholesale Acura
21 wholesale Acura
34 wholesale Acura
37 wholesale Acura
56 wholesale Acura
57 wholesale Acura
72 wholesale Acura
510 retail Audi
1 wholesale Audi
3 wholesale Audi
7 wholesale Audi
12 wholesale Audi
16 wholesale Audi
18 wholesale Audi
23 wholesale


Here's the output I need









Make
Total Count
RetailCount
WSCount

Acura
798
510
288

Audi
256
75
181

View 4 Replies View Related

Count(*) And Select In The Same WITH Statement

Apr 22, 2008

Hi,

I have a query:

-- main select
WITH Orders AS
(
SELECT
ROW_Number() OVER(MyDate ASC) RowNo,
** rest o the query ***
)
SELECT *
FROM Orders
WHERE RowNo BETWEEN 100 AND 200
ORDER BY RowNo

--count of records
DECLARE @COUNT INT
SELECT @COUNT = COUNT(*)
FROM ** the same query as above ***
RETURN @COUNT


In this case it can happen that when counting records there will be different number of records that it was at time of paging. Also server has to execute this query twice and the query is quite complicated means that takes time.

Is there any better way to get number of rows in the same part of query with paging ?


Thanks for help
Przemo

View 12 Replies View Related

Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement

Feb 19, 2008

I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN
SELECT Code, COUNT(Code)as exprCount1a
FROM dbo.[Test]
WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code
END
The results of the statement:
Code | exprCount1a 
1              22              44              1
I would like the following results:
Code | exprCount1a 
1              22              4
3              04              1
Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results.
Thank you in advance

View 2 Replies View Related

SProc - Ad Hoc Sql Statement With COUNT For Exec(@SQL) ??

May 14, 2001

Hello,

I need to get the count into a local variable:

Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause

Exec(@SQL)

It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.

Any help greatly appreciated,
Judith

View 4 Replies View Related

Count # Of Results From SELECT Statement

Jun 6, 2008

Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View 2 Replies View Related

Help With Query (count With Case Statement)

Mar 29, 2008

Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")


any help much appreciated..

thanks!
mike123

View 7 Replies View Related

SQL Statement Count NULL Values

Jul 20, 2005

Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

SQL Server 2012 :: Count Value In Case Statement

Aug 5, 2014

My table structure like below.

id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes

Want to calculate yes count on any day for same id

View 5 Replies View Related

SQL 2012 :: Estimate Row Count - WHERE Clause Of Statement

Oct 16, 2014

How SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-

WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)

I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.

View 2 Replies View Related

TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'

Jul 23, 2005

I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,

View 18 Replies View Related

Count The Number Of Rows In A UNION ALL Statement

Jan 8, 2007

Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark

View 1 Replies View Related

SQL Statement, Adding Two COUNT/CASE Statements

Dec 12, 2007



SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())

=======================

How do I add up both COUNT/CASE columns? Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total


I tried this and it doesn't work. I get invalid column names error for both.

I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total

You would think that there would be some gui functionality in VS08 that would do this...


Thoughts are greatly appreciated!

TT

View 8 Replies View Related

Zero Count Values Not Appearing In SELECT Statement

Sep 17, 2007

Hi all

I have the following tables:




Code Snippet
CREATE TABLE #Lkp_Circle
(
ID INT ,
Abbreviation varchar(50)
)
GO
CREATE TABLE #Lkp_OtherCircles
(
Circle varchar(50)
)
GO
CREATE TABLE #Tbl_User
(
ID INT,
Name VARCHAR(50),
IsActive bit
)
GO
CREATE TABLE #Tbl_UserDetails
(
AssociateID INT,
CircleID INT
)
GO
INSERT INTO #Lkp_Circle VALUES (1,'C1')
INSERT INTO #Lkp_Circle VALUES (2,'C2')
INSERT INTO #Lkp_Circle VALUES (3,'C3')
INSERT INTO #Lkp_Circle VALUES (4,'C4')
INSERT INTO #Lkp_Circle VALUES (5,'C5')
INSERT INTO #Lkp_Circle VALUES (6,'C6')
INSERT INTO #Lkp_Circle VALUES (7,'C7')
GO
INSERT INTO #Lkp_OtherCircles VALUES ('C3')
INSERT INTO #Lkp_OtherCircles VALUES ('C4')
INSERT INTO #Lkp_OtherCircles VALUES ('C5')
INSERT INTO #Lkp_OtherCircles VALUES ('C6')
GO
INSERT INTO #Tbl_User VALUES ( 101,'U 1','True')
INSERT INTO #Tbl_User VALUES ( 102,'U 2','True')
INSERT INTO #Tbl_User VALUES ( 103,'U 3','True')
INSERT INTO #Tbl_User VALUES ( 104,'U 4','True')
INSERT INTO #Tbl_User VALUES ( 105,'U 5','True')
GO
INSERT INTO #Tbl_UserDetails VALUES(101,3)
INSERT INTO #Tbl_UserDetails VALUES(102,4)
INSERT INTO #Tbl_UserDetails VALUES(103,5)
INSERT INTO #Tbl_UserDetails VALUES(104,5)
INSERT INTO #Tbl_UserDetails VALUES(105,3)
GO



SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total]
FROM
(
SELECT DISTINCT 'Circle' = CASE
WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation
WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others'
ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount'
FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN
#Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID
WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0
) AS PivotTable
GROUP BY Circle
WITH Cube

DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles

----EXPECTED RESULT
--Circle HeadCount
--C3 2
--C4 1
--C5 2
--C6 0
--Others 0
--Total 5
--
----ACTUAL RESULT
--Circle HeadCount
--C3 2
--C4 1
--C5 2
--Total 5



The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?

View 9 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

Cannot Construct A SELECT DISTINT COUNT... Statement In SSCE 3.1

Oct 4, 2007

Hi everyone,

sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.

Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:

SELECT DeptID, DeptName,

(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND
(Documents.Active = 'True') AS CountOfActive
FROM Dept


Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.

Any thoughts on how I may solve this?

Much thanx!
Shalan

View 15 Replies View Related

Integration Services :: Using If Then Statement In Derived Column To Count Rows?

Jun 4, 2015

there is a way to write an if statement for a derived column to count rows?

for example:

Row 35: PRI      7010
Row 100: PRI 7011

formula that when it gets to row 100. it will go back and look at 35 and use that data if the 7010 = 7010, if not use 7011,,,

View 2 Replies View Related

Case Statement - Count Orders That Fall Into Specific Dollar Buckets

Aug 2, 2013

I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!

For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:

Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]

Is there an easier way to do this, maybe with a loop?

View 11 Replies View Related

SQL Server 2008 :: Incorrect Prefix Error (select Count Statement)

Oct 7, 2015

Naming convention and what am I doing wrong here:

,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:

Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

View 9 Replies View Related

Select Statement With Count Within Another Select

Aug 23, 2013

I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).

Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.

SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2

[code]....

View 1 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

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.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

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'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

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
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

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 Related







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