MS SQL Server :: Count Top N Group Numbers

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

[code[...

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

Count Consecutive Numbers

Jul 23, 2005

I'm trying extract a count of consecutive numbers, or "unbroken" years inthis case, at any particular given time.For example (simplified):CREATE TABLE #Customers(CustNo INT,YearNo INT,IsCust CHAR(1))INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')SELECT * FROM #CustomersCustNo YearNo IsCust----------- ----------- ------999 2006 Y999 2005 Y999 2004 Y999 2003 N999 2002 N999 2001 Y999 2000 YIn 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for2, etc. Ideally I'd feed it a single year to lookupI'm resisting the urge to create cursor here -- anyone have any hints?....Chris.

View 10 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'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
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:

Code:

SELECT
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 Put Serial Numbers For Results In Group

Apr 4, 2012

I need to put the serial numbers for results in group in SQL Server 2000. Please see below:

My input:
procedureid procname
1 A
1 B
2 A
2 B
2 C
2 D
3 A
3 B
3 C

Output I need:
procedureid procname serial_num
1 A 1
1 B 2
2 A 1
2 B 2
2 C 3
2 D 4
3 A 1
3 B 2
3 C 3

Here is my table:

create table po(
procedureid int,
procname varchar(10),
)
insert into po values (1,'A')
insert into po values (1,'B')

[Code] ....

View 11 Replies View Related

SQL Server 2008 :: Getting Row Numbers For Each People Group?

Feb 18, 2015

I have a set of rows in a table like for example

Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34

Now I want to use Row Number function or any way where I can get the result as below

Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2

View 3 Replies View Related

Assigning Group Numbers For Millions Of Data

Mar 27, 2006

I have a table with first name, last name, SSN(social security number)and other columns.I want to assign group number according to this business logic.1. Records with equal SSN and (similar first name or last name) belongto the same group.John Smith 1234Smith John 1234S John 1234J Smith 1234John Smith and Smith John falls in the same group Number as long asthey have similar SSN.This is because I have a record of equal SSN but the first name andlast name is switched because of people who make error inserting lastname as first name and vice versa. John Smith and Smith John will haveequal group Name if they have equal SSN.2. There are records with equal SSN but different first name and lastname. These belong to different group numbers.Equal SSN doesn't guarantee equal group number, at least one of thefirst name or last name should be the same. John Smith and Dan Brownwith equal SSN=1234 shouldn't fall in the same group number.Sample data:Id Fname lname SSN grpNum1 John Smith 1234 12 Smith John 1234 13 S John 1234 14 J Smith 1234 15 J S 1234 16 Dan Brown 1234 27 John Smith 1111 3I have tried this code for 65,000 rows. It took 20 minute. I have torun it for 21 million row data. I now that this is not an efficientcode.INSERT into temp_FnLnSSN_grpSELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,(SELECT 1 + count(*)FROM distFLS AS c2WHERE c2.ssn < c1.ssnor (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =substring(c1.fname,1,1) or substring(c2.lname,1,1) =substring(c1.lname,1,1)or substring(c2.fname,1,1) =substring(c1.lname,1,1) or substring(c2.lname,1,1) =substring(c1.fname,1,1)))) AS group_numberFROM distFLS AS c1JOIN tu_people_data AS c3ON (c1.ssn = c3.ssn andc1.fname = c3.fname andc1.lname= c3.lname)dist FLS is distinct First Name, last Name and SSN table from thepeople table.I have posted part of this question, schema one week ago. Please referthis thread.http://groups.google.com/group/comp...6eb380b5f2e6de6

View 5 Replies View Related

Passing A Coma Delimited Group Of Numbers To A Collection For Sql

Sep 13, 2005

I have a sql statement and one of the arguments I want to pass is a comma delimited set of numbers. It keeps getting turned into a string. How do I keep that from happening. Here is kind of what it looks likeSelect FirstNamefrom Userwhere NameID in (5,6,7)or Select FirstNamefrom Userwhere NameID in (@NameIDList)There is no error code just nothing returns. If I take out the @ANameIDList and put the values I want, it returns the correct results.Thanks,Bryan PS the link to the original thread it here http://forums.asp.net/1046154/ShowPost.aspx

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

Help ... COUNT + GROUP BY

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
SELECT A, COUNT(DISTINCT C) FROM help GROUP BY A
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.

Thanks,

Frank

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
FROM info AS i INNER JOIN
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.
ex.

infid pubid
1 30
1 32
1 33
2 30

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

thanks

View 7 Replies View Related

Sum Of COUNT In Group By

Mar 25, 2004

SELECT emp_id,COUNT(*)
FROM emp
GROUP BY empId

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.

Thanks.

View 4 Replies View Related

Help ... COUNT + GROUP BY

May 29, 2008

hi everyone,

I have a table: Help

A B C
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

SELECT a, COUNT(c) FROM Help GROUP BY a

Result:

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?
Thanks.

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

SQL COUNT, SUM AND GROUP BY

Apr 5, 2006

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

Cheers

View 10 Replies View Related

Count GROUP

Mar 13, 2007

hi,
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
ORDER BY ItemID

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

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

Thanks,
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
HAVING (COUNT(*) > 1)

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

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

View 7 Replies View Related

Help ... COUNT + GROUP BY

May 30, 2008

Hi everyone,

I have a table: Help

A B C
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

SELECT A, COUNT(DISTINCT C) FROM help GROUP BY A

Result:

1> 05/01/2008 2

I need:

1> 05/01/2008 4

Thanks !!!

View 12 Replies View Related

COUNT && GROUP BY

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
SELECT Site, COUNT(*)
FROM dbo.MyTable
GROUP BY Site
-- 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?

Thanks.

View 6 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?

View 1 Replies View Related

Group By And Count Of Each Group

Oct 23, 2007

Hi,
I use group by myItem, that displays rows for each value of myItem.
I want at the end of each group for each specific myItem, I want an extra row to tell me the count of the rows for that group.

How do i do that please. Here s what I need, for example for this set of rows:

myItem colum2 colum3
711056 22662 Finances / -2291 -2479916
711056 22663 Finances / -2380 -2576255
711056 43428 Cda) -323 -349635
711056 43428 . -B-(Cda) -44 -47628
711057 44348 . -B-(Cda) -355 -384273
711057 47033 . -B-(Cda) -1278 -1383384

I need 2 extra rows, one at the end of the items 711056 telling me there are 4 rows for the item 711056 and one row at the very end telling me there are: 2 rows for the item: 711057

Thanks a lot.

View 6 Replies View Related

Count And Group By With DateTime!

Nov 19, 2006

Hi all,
I have a problem with my query which is suppose to select count posts group by the date, the query works but doesnt return the count as i want, i think the problem that the datetime column contains also Time in hours which ofcourse isnt same in all rows in same day, so i dont know what to do, Here's my Query:
SELECT PostID, Date, COUNT(Date) AS 'TotalPosts' FROM Posts GROUP BY PostID, Date
Thank you for help

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

Trying To Get The First Count Of A Group Of Rows

Dec 24, 2003

Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg

Row A = NoteID 1, FK_JobID 1, UnRead

Row B = NoteID 2, FK_JobID 1, UnRead

Row C = NoteID 3, FK_JobID 1, UnRead

I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.

I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.

Heres my attempt

DECLARE @UserID INT

SET @UserID = 4

SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'

Thanks in advance

View 6 Replies View Related

Help With Count And Group By Query Pls!!

Dec 6, 2006

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

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

i have this query

Code:

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

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

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

Code:

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

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

but if i put the query

Code:

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

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

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

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

any help pls pls pls!!!!

View 1 Replies View Related

SQL - Count, Sum + Group By On Three Different Tables.

Jan 18, 2005

Hello To All @ Dev Shed,

I have three tables, say, A, B, and C. Both B and C contain/reference the primary key of A, ie B.a_id, and C.a_id. Multiple rows in B and multiple rows in C can have the same a_id. C and B have no relationships and essentially independant of each other.

I'm trying to find a single query (to prevent having to rewite function interfaces) that can return all the fields of A, the sum of a feilds in B, B.cost, where B.a_id = A.a_id and finially the count of C.a_id 's for where C.a_id = A.a_id.

The query I've been worlking on so far is as follows,

SELECT A.name, A.a_id, SUM (B.cost), COUNT ( DISTINCT C.a_id)
FROM A LEFT JOIN B USING (a_id) LEFT JOIN C USING (a_id)
GROUP BY A.a_id

This produces the correct result for the count of C.a_id (thanks to a DISTINCT) but the sum of B.cost is out by the factor of count C.a_id. I can see why this is happening but have completely run out of ideas. Group by on two columns may be? A strategically placed subquery? Is is even possible?

Any help would be much appreciated. Thanks in advance for your time.

Peter

PS I'm new to this forum thing so if you want the unsimplified query with all the full names just let me know and I'll post it.

View 2 Replies View Related

SELECT COUNT Of MAX (GROUP BY)

Jul 16, 2006

Hello

I want to get the COUNT of

SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View 4 Replies View Related

Running Count By Group

Nov 28, 2007

I'm starting to think I can't do this with a SELECT....but, any input would be great!

I'm trying to get a running count by group....meaning, my data looks like this (two columns):

john.doe@yahoo.ca X
john.doe@yahoo.ca Y
john.doe@yahoo.ca M
elvis@gmail.ca A
kid.rock@hot.ca X
kid.rock@hot.ca Y

..and I want to add a column like this (running count of codes by email address):

john.doe@yahoo.ca X 1
john.doe@yahoo.ca Y 2
john.doe@yahoo.ca M 3
elvis@gmail.ca A 1
kid.rock@hot.ca X 1
kid.rock@hot.ca Y 2

thoughts? ...thanks in advance.
david.

View 3 Replies View Related

Sum, Count And Group By In The Same Query

Apr 16, 2008

Hi,
I have two tables in my DB:
tbl_Users: callSign(Char), FirstName(Char), LastName(Char)
tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.

The result that I want to see is:

CALL SIGN Last Name First Name Date Number Of Events TotalKM
111 MR. X 01/01/2008 3 40

I know this:
The number of events is countable.
if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.

But how do I group it together?!

Thanks,
Gil

View 2 Replies View Related