How To Count Column ?

Jan 22, 2008

HistoryLog Table


MainIndex

Sales

Date

1
John

Jan-05-1999

2
John

Jan-07-1999

3

Liz


Jan-08-1999

4
Yumi

Jan-08-1999



HistoryLogDetail Table


DetailIndex
MainIndex 


Date

1
1

Jan-05-1999

2
1
Jan-07-1999

3
2

Jan-08-1999

4
3

Jan-08-1999



select * from HistoryLog  
I will got a return result with Mainindex �Sales�Date 3 kinds of columns Data , If I need to add one column by myself let the result like below
 Mainindex ã€?Salesã€?Dateã€?Total
the Total's value is from  count(MainIndex) of  HistoryLogDetail Table , result will look like below
 
MainIndex   Sales         Date                Total

1                  John         
Jan-05-1999      2       →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 2)

2                  John         
Jan-07-1999      1      →(because this record's index 1 exist in HistoryLogDetail Table twice so the total is 1)
3                   Liz          
Jan-08-1999       1

4                   Yumi       
Jan-08-1999       0
 
I trid wirte a code to got the total column but what I got is like this
MainIndex   Sales         Date                Total


1                  John         
Jan-05-1999      4


2                  John         
Jan-07-1999      4
3                   Liz          
Jan-08-1999       4


4                   Yumi       
Jan-08-1999       4
 
what the columns of Total shows is .. how many record are in  HistoryLogDetail Table but that's not I need ..
can you help?
thank you very much 
 

View 7 Replies


ADVERTISEMENT

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

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

2nd Count Column

Oct 29, 2007

Hey there,

I am new to SQL so please bear with me. I was wondering how to create a new column that counts the EntityId COUNT(CdsQuotes.EntityId) and puts it into a seperate column.

What I am trying to achieve is a count of entity IDs from one table and a count of entity IDs from another table - but the 2nd count must be from the first counts results (hope this makes sense).

So it returns a count from table 1 and then a corresponding count from table 2 (based on table 1 entityIDs)

Any ideas?

SELECT

Entity.EntityName,
SuspectBond.EntityId,
COUNT(SuspectBond.EntityId) as "Suspect Bonds"

FROM SuspectBond

INNER JOIN Entity ON
SuspectBond.EntityID = Entity.EntityId

WHERE
SuspectBond.StatusId = 1

GROUP BY
SuspectBond.EntityId,
Entity.EntityName

ORDER BY
COUNT(SuspectBond.EntityId) DESC

View 6 Replies View Related

How To Get The Column Count

May 6, 2008

hi,

I have a table as below

id col2 col3 col4 col5 col6 col7
1 2 2 2 2 3 4
2 3 6 2 2 2 5
3 2 3 4 2 2 6
4 3 2 2 2 9 8
5 2 3 4 6 2 2


I want to know the number of columns which is having the value 2, in each row

can anyone help?

View 2 Replies View Related

Update Count Column

Apr 24, 2008

Hi I have added a new column to my table, i need to run a query and update this colmn with a count, so like

i = 1

loop

update table
set column = i
where column = 113

i=i+1

loop

just not sure what the sql syntax is?

View 20 Replies View Related

Return Column Name And Count

Nov 3, 2013

I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:

The following query:

SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID

Returns this result:

Category
BMS
BMS
Water
BMS
BMS
Air

And the following query:

SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID

Returns this result:

AssetQty
4
1
1

I need to have both of those results returned, as a single result. Such as:

Category AssetQty

BMS 4
WATER 1
AIR 1

However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:

Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.

View 5 Replies View Related

Count Changes On One Column In A Table

Oct 26, 2007

I've a nub question that someone probably has the fast answere too.

How do I count the number a columnvalue in a table has changed? I was starting to write a stupied cursor but there has to be a much smarter way.

I've a case where I need to count the number of times the salary in a table for individual employees changes.

Thanks in advance!

View 2 Replies View Related

Transact SQL :: Count As Per Column Value

Jul 2, 2015

I have table like below

col1 volume
A 22
B 28
C 1
E 3
D 4

Output should be like below

col1 vol
A 22
B 28
Others 8

View 4 Replies View Related

Add SQL Count Column To Existing Table

Apr 22, 2008

I have a table for blog comments I want to add a column that counts the number of comments for each article.
existing table looks like this:
CommentID
ArticleID (FK)
commentAuthor
authorEmail
comment
commentDate (getDate())
I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool:
"SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)"
But can I add that to the table somehow so it does it automagically???

View 5 Replies View Related

How To Count Particular Text (/) From A Column In A Table?

Jun 16, 2008

I m using SQL Server 2000.
I have Tabel named Topic and have a column name lineage.  lineage has data like following:
//////546707//546707//546707/43213/
Now I want to get records who has only one "/" in it's crreponding lineage column.
 Can somone tell me how to do that in SQL Server 2000?
Thanks
Khushbu

View 2 Replies View Related

Query Duplicate Column Value Row Count

Sep 5, 2012

How do I count the number of alike values in a column and have that value as a count column.

Example using Col3:
Col1 Col2 Col3
A 12 Test
B 45 Test1
C 45 Test
D 12 Test
E 10 Test1
F 11 Test2

Result
Col1 Col2 Col3 Count
A 12 Test 3
B 45 Test1 2
C 45 Test 3
D 12 Test 3
E 10 Test1 2
F 11 Test2 1

View 6 Replies View Related

Need To Count And Rank NVARCHAR Column

Apr 2, 2008

Assuming that I have a table similar to this simplified version:

CREATE TABLE [SomeTable] ([Field1] NVARCHAR(50));

How could I count and rank the instances of specific values in the "field1" column? For example, let's say that I want to show the frequency of the column values like so:

field1 count
some value 24
another word 20
a value 5

(and so on...)

How can I do this?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

View 3 Replies View Related

How To Count Number Of Value Occurrence In Column

Oct 24, 2013

I am having following data:

c1c2c3
122401
1221072
122833
122793
122813
122314

[code]....

I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.

Output
c1p1p2p3
122933

View 6 Replies View Related

How To Find Count From Multiple Column

Mar 24, 2014

let us suppose

ID | companycode | year | name | State
1 | 01 | 2013 | xyz | D
1 | 01 | 2013 |abs | S
23| 02 | 2014 | age | D
2 | 01 | 2013 | XYZ | D
1 | 01 | 2013 | abh | D
7 | 13 | 2014 | adi | C
7 | 13 | 2014 | adi | C
7 | 13 | 2013 | adi | C
7 | 13 | 2014 | adi | C
7 | 13 | 2013 | adp | C
7 | 13 | 2014 | abp | C

and we want like this

ID | companycode | year | countname | State
1 | 01 | 2013 | 2 | D
23| 02 | 2014 | 1 | D
1 | 01 | 2013 | 1 | s
2 | 01 | 2013 | 1 | D
7 | 13 | 2013 | 2 | C
7 | 13 | 2014 | 4 | c

View 4 Replies View Related

Updating A Column With A Count From Another Table?

May 18, 2014

My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.

e.g. TABLE2:

e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);

--^Doesn't work

so my TABLE1 should be:

e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0

(The .. is just spacing to show the table here)

View 1 Replies View Related

Count Query Against A Column Over Last 6 Month?

Mar 3, 2015

I have a few million rows of a data in a table that store indexing information for scanned customer documents. Fields like last name, first name, document description (descript), document type and scan date (scandate).

I want to be able to query the document description column where it'll show how many times each document description was scanned/used over the course of the last 6 months or year.

View 3 Replies View Related

Column Count From Stored Procedure

Jul 20, 2005

I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!

View 2 Replies View Related

COUNT (Distinct Column) = 0 With GROUP BY

Mar 20, 2008

I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN



0 "

I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.

------------------------------------
SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek,
COUNT(DISTINCT UserID) AS NumberOfUser

FROM testUserLoginDuration
WHERE Archived = 0
GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3))
ORDER BY
CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6
WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7
END

--------------
returns

MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1

View 4 Replies View Related

How To Get Maximum Count Of A Column In A Table.

Dec 6, 2007

i have a table with productID and OrderID. For ech product there are orders.
So for each productID there are lot Of OrderID's are present.
My data like

ProductID OrderID

1 12012
1 12447
1 12478
2 24154
2 21457 etc.......

so, i need to get the maximum count for a product.

i can do by using Temporary tables. but i need without using temporary tables.

please help me out.

thanks
-Praveen.

View 12 Replies View Related

Column Count Max For Merge Replication Still 246?

May 16, 2007

Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?

View 4 Replies View Related

How Do Make Sure If Column Is Null Before I Count It? ( Use SQL 2000)

Apr 29, 2008

 the Table columns is like this NO          ProductNo              Area            In               Out1               0001                     US             NULL          NULL2               0002                     UK             NULL          Y3               0003                     FR              Y                NULL 4               0004                     FR              Y                NULL5               0005                     UK              Y                NULL I have Query get the result belowArea       Count         In&OutUS           1UK           2FR           2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is  Null , if one of them is  Null the plus 1 so the result would like  I have Query get the result belowArea       Count         In&OutUS           1                 1
UK           2                 1
FR           2                  0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you   

View 4 Replies View Related

Verifying A Table's Column Count Across Servers?

Jan 31, 2005

Hi folks!

The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.

The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.

I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:

So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.

For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.

Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns
where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns
where table_name = 'Dest')) as ColumnDiff

Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.

Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.

Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?!
Paul

View 4 Replies View Related

Column Count Percentage Of Not Null Fields

Jul 27, 2006

Hello folks,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'

I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.

Thanks for your help
Mike

View 2 Replies View Related

Count How Many Times Column Is Populated With A Number Per ID

Jul 10, 2013

What i want to do is count how many times the [Omnipay_Account] column is populated with a number per parentid.

In the [Omnipay_Account] column you can either have a null or a 15 digit number

Idea result layout would be three columns these columns would be

Every row, would need to be grouped via the parentid number

ParentId Omnipay MSIP

My query is

SELECT
[ParentID]
,[Omnipay_Account]
FROM [FDMS].[dbo].[Dim_Outlet]

View 3 Replies View Related

Count The Number Of Null Values In A Column

Feb 3, 2008

Hi,

I have a table employee with 4 columns,

empno fname lname deptno

1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50

suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?

select count(deptno) from employee
where deptno IS NULL..

When i query this i get the result as 0..

View 8 Replies View Related

Create A Column In RecordSet With The Record Count...

Jan 15, 2007

Hello

I'm reading a XML file and the next operation need a column with the row count.

I don't want to know how many rows there is in the recordset, but, the row count of each record.

How can I implement this?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

View 1 Replies View Related

Data Flow Transformation: Count And Sum The Column Value

Nov 8, 2007

Hi,

I have one data flow control. Source is SQL server and destination is flat file destination. I have one derived column placed in between these two. This functionality works fine. I would like to sum one column data and count total no. of columns and put it in global variable. How can I achieve it?

Thanks,

View 7 Replies View Related

How To Find Running Count Of String Column

Jul 22, 2015

Below are the columns I have in table Customers.

Date CustomerID
7/14/2015 AAA
7/14/2015 BBB
7/15/2015 AAA
7/15/2015 BBB
7/15/2015 CCC
7/16/2015 AAA
7/17/2015 AAA
7/18/2015 AAA
7/19/2015 AAA

What I need is to find running sum of count of customerIDs. here CustomerID is string. So, the expected result is

Date Run_Sum
7/14/2015 2
7/15/2015 5
7/16/2015 6
7/17/2015 7
7/18/2015 8
7/19/2015 9

View 6 Replies View Related

How Count Column In Pivot Table- And Add Result Row

Jan 20, 2008

how count column in pivot table- and add result row
i need to calculate each column
for example
day1 day2 day3 day4 day5
-------------------------------------------------------------------------
1 2 1 2 3
1 2 3 2 2
2 3 2 1 2
2 3 0 0 0
-----------------------------------------------------------new result row
ok ok 1|2|3 1 3

i need to check each column
if i have twice each number
if not show the missing number
TNX




Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

IF @Numdays > 28
BEGIN
SELECT p.ID,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END

View 12 Replies View Related

SQL Server 2012 :: Record Count Of Column Field

Oct 30, 2014

I have created table in which there are four columns (id, date, parcelname, parcelnumber) and 10 rows. I want to count record of the column parcelnumber but condition that, in between two different dates the record should be counted.

View 9 Replies View Related

Need Help With Adding A Duplicate Record Count Column To Query

Jul 23, 2005

I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn

View 2 Replies View Related

Error 209 Ambiguous Column ..Transaction Count After EXECUTE Indicates .....

Aug 28, 2006

i'm getting following exception when i try to execute stored procedure.

{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }

I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"

In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN

/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */

here is my stored proc

CREATE PROCEDURE dbo.ExportFile
@intMonth INT,
@intYear INT,
@dtFirstDayOfMonth DATETIME

AS

BEGIN

BEGIN TRANSACTION


/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/

UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)

IF @@ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback

DROP TABLE #TEMPRESULT
IF @@ERROR<>0
GOTO ERR_HANDLER

COMMIT TRANSACTION
RETURN 0
END

ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO

View 8 Replies View Related







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