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


ADVERTISEMENT

Built In Increment Count In SQL?

Aug 21, 2000

Does anyone know if SQL has a built in function for returning increments of rows. For instance; I have a table with 100,000 rows, but would like to return 512 rows at a time. I've looked at the documentation and haven't came across anything that will do this. Any help is appreciated.

View 4 Replies View Related

Increment Download Count Select And Update Together.

Mar 25, 2006

Hi is there away of doing both my select and update of a field in my database (m not using store procedures)
 
Basically all I need to do is get the value of DownloadTimes then add 1 and re update it.
Also to stop missing increments what the best way to lock the database while an update is in progress?
 
This is basically firing after a user clicks a file to download.  I bring up the dialogue for them to save it is it possible to get which button they press and what is the best way to deliver it to them eg response.writeFile()??
 
Thanks sorry about all questions.

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

Transact SQL :: How To Update A Field And Insert Increment Count

May 28, 2015

I want to update a field and in this field insert a increment count, for example:

When I make, "Select * from Users order by User" displays:

User1  |  NULL
User1  |  NULL
User1  |  NULL
User2  |  NULL
User2  |  NULL

and I want to do this:

User1  |  1
User1  |  2
User1  |  3
User2  |  1
User2  |  2

how to do this?

View 7 Replies View Related

Increment In Select Statement

Nov 29, 2007

Hi,

Following is a query which gives me an output as mentioned below. Can anybody help me out, in giving increment numbers, e.g. 1,2,3,4... to the first column in the select statement.

use account
select 'PT','JE01','71','1','1', Vou_date=convert(varchar,vdt,101), cltcode, '994801', vamt, 'ZJE01','JOURNL', '0.00',
narration, DrCr = (case when drcr = 'D' then 'D' else 'C' end),
'','','71','1','P',',',',',','
from ledger where cltcode = '5555'
and vdt >= 'Apr 1 2007'
and vtyp = '15'
order by vdt

OUTPUT

PTJE01711120070419A9986

View 9 Replies View Related

Transact SQL :: SELECT STATEMENT With Increment

Jun 23, 2015

How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.

Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')

Result from the above query

serial_no  Scode  Sdate  SErr
1 1002  10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT

Desired SELECT STATEMENT Result with increment of serial_no  (Max(serial_no+1)

Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'

View 3 Replies View Related

SQL Statement For Inserting Date && Auto-increment Fields

Dec 8, 2006

I have a (1) date field and (2) an auto-incrementing ID field that always throw me errors when I'm doing a programmatic insert.
 
(1) After doing many searches on the subject, I don't think I'm using the correct syntax for the date and can't find any suggestion that works. Would appreciate your knowledge on correct SQL syntax for inserting a "today's date" field.
 
(2) I'm using the following code to create a new auto-incrementing ID for each record but it seems that there should be a smoother method to force the field's value to auto-increment. Any ideas?
 
Private objCmd As SqlCommand Private strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("select max(ClientID) from tblClients", strConn)Dim intClientID As Int16 = objCmd.ExecuteNonQuery + 1

View 5 Replies View Related

Select Into Statement Without Taking Auto Increment Of ID Column To The New Table

Mar 7, 2011

Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.

The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries

Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
 
btw I have used this statement

SET
IDENTITY_INSERT MyTable

ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...

View 4 Replies View Related

Need To Have 2 Auto Increment Columns (Seed, Increment)

Dec 11, 2007

Hello,

I Have a table that needs to have 2 unique number.

detail_id and detail_print_id.

detail_id is already an IDENTITY.

both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).

So I thought i could do the following:

detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah

that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.

is there anyway I can create a int column to auto increment, without the column being an IDENTITY??

also, I would prefer to not have to create a second table with a single column just for this work.

Thanks,
Justin

View 5 Replies View Related

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 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

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







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