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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 19, 2013
I have a table of people:
Name, City, Sex
I want a table where each line is the city, and the number of Males and Females.
For example, this is what I tried, and I know it's wrong:
Select City, count(name) where Sex = 'M' as NumberM,
count(name) where Sex = 'F' as NumberF
Group by City
How do I do this?
View 3 Replies
View Related
Jul 23, 2005
Does anyone have any recommendations on how to solve the following?I would like to have a query that selects ALL columns from a database,however only records that have a count of a certain column which isgreater than 1 when the results are grouped by a few columns. I knowthe following query doesnt work (because it contains items in theSELECT that arent in the GROUP BY), but its the jist of what I need todo.select a,b,c,d,e,f,g,hfrom table1group by a,b,c,dhaving count(e) > 1Can anyone help me out with this?
View 3 Replies
View Related
Nov 27, 2007
I pulled data from several tables into temp table and I want to write a query to do count specfic data in certain columns and group it by the Owner... here is a sample table and query below, however in the real table there are more owner's, so I want to avoid using statements like - where owner = 'Smith'
table1
------
CID |Owner|Color|Data|NextAction
----------------------------
1234|Smith|Blue |A |Level1
5678|Jones|Green|C |Level3
9012|Smith|Blue |A |Level2
0987|Smith|Red |c |Level1
4567|Jones|Green|B |Level3
etc...
Query
------
Select distinct(owner),
(Select count(Color) from #table1 where Color = 'Blue') Blue,
(Select count(Color) from #table1 where Color = 'Green') Green,
(Select count(Color) from #table1 where Color = 'Red') Red,
(Select count(Data) from #table1 where Data = 'A')A,
(Select count(Data) from #table1 where Data = 'B')B,
(Select count(Data) from #table1 where Data = 'C')C,
(Select count(nextaction) from #table1 where nextaction = 'Level1')Level1,
(Select count(nextaction) from #table1 where nextaction = 'Level2')Level2,
(Select count(nextaction) from #table1 where nextaction = 'Level3')Level3,
from #table1
group by owner
Does't really work... I tried putting a group by in the sub select but I get and error because I get multiple rows and that is not allowed when you use a sub query as an expression... so it said..
OUTPUT Should Look Like
Owner|Blue|Green|Red|A|B|C|Level1|Level2|Level3
----------------------------------------------------
Smith| 2 | 1 | 1 |2|0|1| 2 | 1 | 0
Jones| 0 | 2 | 0 |0|1|1| 0 | 0 | 2
Thanks
For any help in advance
View 3 Replies
View Related
May 1, 2007
Hello.
I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
Thanks.
View 1 Replies
View Related
Jun 27, 2007
I have a group in my report. This group is showing only the Top 50 results. Inside of group row is a total column. I would like to display the total of the totals in the table footer. Something like this:
This is my data:
Creature Kind Number
Frog Amphibian 3
Cat Feline 10
Lizard Amphibian 20
Cow Mammal 8
Group is on Kind. Limited to the Top 50 Sum(Number). So I want the report to look like this:
Amphibian 23
Feline 10
Mammal 8
Total Creatures 41
I can't get the correct Total Creatures because the data is limited to the Top 50 Sum(Number). So right now Total Creatures is adding up to be every creature in the database. I just want the Total Top 50 Sum(Number) Creatures.
I hope that makes sense.
Sarah
View 1 Replies
View Related
Jun 12, 2007
Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.
View 4 Replies
View Related
Jan 18, 2003
Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.
I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.
I tried the follwoing, but get incorrect results.
select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price
However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price
I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?
View 2 Replies
View Related
