T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,


I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,


View 1 Replies


Create Record Based On Count

Nov 9, 2007

I have the following data,

CustomerID EngID EngCount
1 A11 2
2 B12 1
3 C10 3

I need to display it as,

CustomerID EngID EngCount
1 A11 1
1 A11 1
2 B12 1
3 C10 1
3 C10 1
3 C10 1

Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.

How to do?

View 5 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:




View 3 Replies View Related

Updating Field Based On Record Count

Oct 18, 2004

I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.

Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc

All help is greatly appreciated.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Group Total Count Of Similar Items

Mar 30, 2015

We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.

So some sample records of a complete package would look like this:

ItemId | ParentId | Name | QtyAvailable
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3

ItemId's 2 & 3 are items contained within the ItemId 1 package.

Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:

Name | Available Qty
Package A | 15
Package B | 100

How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?

View 6 Replies View Related

T-SQL (SS2K8) :: Wrap Varchar Field Based On Character Count And Spaces

Dec 8, 2014

Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.

--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')

[Code] .....

View 5 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

SQL 2012 :: Fast Way To Do Group By Count On Items Within Group?

May 1, 2014

select top 15 count(*) as cnt, state from table
group by state
order by cnt desc


Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

View 9 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 

View 1 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:


DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date

The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

How To Use Group By (group Tasks Based On Projects)

May 28, 2007

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:









how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task------------->columns are projectid, taskid


View 4 Replies View Related

Update A Record Based Of A Record In The Same Table

Aug 16, 2006

I am trying to update a record in a table based off of criteria of another record in the table.

So suppose I have 2 records

ID owner type

1 5678 past due

2 5678 late

So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?

View 5 Replies View Related

Value Of A Record Based On A Previous Record

Jul 20, 2005

I hope you can help me. I posted this in the microsoft sql server newsgroupa few days ago and got no response so I thought I'd try here. If I canprovide any clarification I'll be glad to do so.I'm trying to calculate a column based on the value of the previous record.I'm not very experienced with SQL-Server.I'm using the following table:CREATE TABLE tblPayment([PaymentID] [int] IDENTITY (1, 1) NOT NULL ,[LoanID] [int] NULL ,[PaymentPeriod] [int] NULL ,[PaymentRecDate] [datetime] NULL ,[PaymentAMT] [money] NULL)I have a view based on this table. That view has the following calculatedcolumnsBeginningBalance: For the first record, this is equal to the loan amountfrom the loan table. For each additional record this is equal to the endingbalance from the previous payment record.Interest: BeginningBalance * the monthly interest rate from the loantablePrincipal: PaymentAMT - InterestEndingBalance: BeginningBalance - PrincipalIt might seem I could use a subquery to calculate the Beginning Balance asin:SELECT LoanID, PaymentPeriod, PaymentAMT,(SELECT SUM(PaymentAMT) FROM tblPayment AS tbl1WHERE tbl1.LoanID = tblPayment.LoanID AND tbl1.PaymentPeriod <tblPayment.PaymentPeriod) AS BeginBalanceFROM tblPaymentWHERE (LoanID = @LoanID)But this will not work, because the interest is calculated on the previousmonth's balance. I need to find a way to loop through the recordset. Isthis possible?Thank you,--Derek CooperDatabase9www.database9.com

View 5 Replies View Related

T-SQL (SS2K8) :: Creating Database Where Each Record Is Required To Have Twin Record In Database

May 12, 2014

,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.

Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356

Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?

View 8 Replies View Related

Getting Top 5 Based On The Count?

Aug 19, 2013

I am using the following query

select top(5) COUNT(incident),name from company_info group by name
order by COUNT(incident) desc ;

to get most repeated incidents.


I want to get the same output with out using order by query.

View 4 Replies View Related

T-SQL (SS2K8) :: Set Value Based On Previous Row

Apr 11, 2014

I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.

Anyway the code is....

declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)

[Code] ....

So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.

Next the rule is coded in the case statement above.

So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.

View 6 Replies View Related

Group By And Count

Dec 21, 2007

I want to know how to merge the following data.  I am using 4 queries below.  I was hoping to do it with 1 query.   Table1Dist     Fund     VAE    AOVAW   AOMD      CourtMD     JudgeCAC   AOCAC   CourtVAE   JudgeVAE   JudgeI want to join the following 3 queries:DcountAll                                                                                                   DcountAOSelect Dist, Count(Dist)as Count from Table1  GROUP BY Table1.Dist           Select Dist, Count(Dist) as Count from Table1 Where Dist='AO' GROUP BY Table1.DistDcountCourtSelect Dist, Count(Dist) as Count from Table1 Where Dist='Court' GROUP BY Table1.DistSELECT DCountAll.Dist, DCountAll.Count, DcountAO.Count AS AO, DcountCourt.CountFROM DcountCourt RIGHT JOIN (DcountAO RIGHT JOIN DCountAll ON DcountAO.Dist = DCountAll.Dist) ON DcountCourt.Dist = DCountAll.Dist;

View 2 Replies View Related


May 30, 2008

hi everyone, I have a table: Help
A               B    C05/01/2008 100 1 05/01/2008 100 205/01/2008 100 205/01/2008 200 1 05/01/2008 200 2 
Result:1> 05/01/2008 2I need:1> 05/01/2008 4Thanks !!!

View 4 Replies View Related

How To Count GROUP BY

Nov 3, 1998

TO all SQL Gurus,

The following statement returns let say 4 records -

SELECT title, price FROM table GROUP BY title

what is the correct syntax for the statement to return the count of 4 ?

if I put ->

SELECT count (*) FROM table GROUP BY title

it'll return 1.



View 4 Replies View Related

Help On Count And Group

May 23, 2008

Hi everybody I have this sql code

SELECT i.infid, i.infname, i.infcalled, p.pubinfid, p.pubpub, p.publang, p.pubcount, p.pubid, n.cdpldesc
pubssubscribe AS p ON i.infid = p.pubinfid INNER JOIN
newpubs AS n ON p.pubid = n.pubid
WHERE (i.infcond IS NULL)AND (p.pubid BETWEEN 30 AND 33) AND (p.pubcount > 1) AND (NOT (p.publang = '.'))

there are many records where infid appears more than once because people could subscribe in different in different publicatons.

infid pubid
1 30
1 32
1 33
2 30

etc... I want to count the infid appearing once and group it with infid


View 7 Replies View Related

Sum Of COUNT In Group By

Mar 25, 2004

SELECT emp_id,COUNT(*)
FROM emp

THe above query returns the values

empid COUNT
1 4
2 4
3 5

BUT i want sum of the count (13) in the same query. How to achieve this.


View 4 Replies View Related

Help ... COUNT &#043; GROUP BY

May 29, 2008

hi everyone,

I have a table: Help

05/01/2008 100 1
05/01/2008 100 2
05/01/2008 100 2
05/02/2008 200 1
05/02/2008 200 2



1> 05/01/2008 3
2> 05/02/2008 2

But I need grouping columns B and C so that the result was

1> 05/01/2008 2
2> 05/02/2008 2

Is it possible? How can I do?

View 5 Replies View Related

Count Of Group By?

Jul 29, 2013

I want to join two tables and count the rows on the second table as something is grouped by the first. To be more clear. I have vendors and open tracking numbers for orders they have shipped. I want to list the vendor information and to group by the vendor. However I also want to count how many open orders that vendor has - which is on a different table. I have this so far:

SELECT `companyName`
, `emailAddress`
, `ccAddress`
, `dailyMessages`
, (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') AS 'openTracking'
FROM `vendor`
LEFT OUTER JOIN `tracking`
ON `vendor`.`id` = `tracking`.`vendorID`
WHERE (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') > 0
GROUP BY `vendor`.`id`

The problem is that this code results in this table. Where openTracking is always equal to the total count, not distinct to that vendor's ID

View 3 Replies View Related


Apr 5, 2006

I'm putting together a football database and want to show how many games a player played in a season.

SELECT COUNT (PlayerFixture.FixtureID) FROM PlayerFixture WHERE PlayerFixture.PlayerID = '::PlayerID::' GROUP BY Season

However if a player doesn't play any games in a particular season the COUNT function ignores it and just returns values in seasons he has played. Is there any way i can get the COUNT function to return a "0" or "-" if a player didn't play any games that season?


View 10 Replies View Related


Mar 13, 2007

this sounds simple, but ive to idea how ..
1. how to count all records we found when there's group by?
SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership

2. how to print rows number for each record? like the Expr1 column
Expr1 ItemID CustomLotNo Ownership
1 A A01 INT1

~~~Focus on problem, not solution~~~

View 12 Replies View Related

Sub Group Count

Apr 24, 2008

I am doing employee shift report. I am showing totals for the shift, day and store. Store is the main group, day is the sub group under store group and shift is the sub group under the day group. I want to calculate number of shift group records (Number of shifts) and use it in the store level group calculation.

How to do it?

Muniappan Kandasamy

View 1 Replies View Related

Group By Count * &&>1?

May 23, 2007

Can this be used to prevent the repetition of records displayed in a page?

Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

View 1 Replies View Related

Group By Having Count(*)

Jan 4, 2008

I would like to select data from a database using 'Select * " based on a value in a row (same column)being unique.
By that I mean that that data must not repeat again. Idealy I would like to set the number my self so rather then unique I could say

select the rows from the database only if the uniquevalue does not repeat more then x number of times.
Value A
Value B
Value C
Value B
Value B
Value C

So if I wanted to set the uniquness to 1 then only row with Value A would be collected.
If I set the uniquenss to =<2 then I would get data from rows with value A and C so
3 rows returned.

I have this so far

FROM SingleS.mdb
GROUP BY Uniquevalue
HAVING count(*) = 1

View 7 Replies View Related


May 30, 2008

Hi everyone,

I have a table: Help

05/01/2008 100 1
05/01/2008 100 2
05/01/2008 100 2
05/01/2008 200 1
05/01/2008 200 2



1> 05/01/2008 2

I need:

1> 05/01/2008 4

Thanks !!!

View 12 Replies View Related


Oct 25, 2007

Probably has been solved a million times, but here it is anyway:

Say I have a table with follwoing rows:

Site Appointment Maintenance Date
NY 13 10-25-2007
CA 14 10-29-2007
NY 18 10-25-2007
NJ NULL 10-26-2007

I need to perform a simple count showing the total (Maintenance Dates) for the next 2 days. Additionally, if a site is listed, but it doesn't have any (Maintenance Dates) for the next 2 days, it must be part of the report with the total as zero.

I can do
FROM dbo.MyTable
-- for brevity, assume GETDATE() is set to 12:00 AM, today
WHERE [Maintenance Date] >= GETDATE() AND [Maintenance Date] < DATEADD(dd, 2, GETDATE())

but this only lists NJ and NY, because they both have counts over the next days. How do I get CA in the list?


View 6 Replies View Related

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