How To Count Rows By Month
Oct 20, 2014
I am trying to get the number of rows for each month.
table name:sitez
ID Name crDate access
===========================
1 Bob .. 2014-01-11 .. 1
2 Jerry .. 2014-01-22 .. 2
3 Jim .. 2014-05-06 .. 1
4 Jason .. 2014-12-11 .. 1
5 Jen .. 2014-11-21 .. 3
I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)
This should return:
1
0
0
0
1
0
0
0
0
0
0
1
However when testing the script I was only getting back:
1
1
1
which didn't knowing which months were 0
By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.
When I query the database, the results for December 2014 shows '13' when I execute:
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)
There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3
Why I'm seeing ghost results?
All I need is 12 results, ordered by crdate month.
View 3 Replies
ADVERTISEMENT
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
May 13, 2015
I have
Issues | Category | Date
I1 | A | 1/1/2015
I2 | A | 2/2/2015
I3 | B | 2/1/2015
I4 | C | 3/3/2015
I5 | B | 4/3/2015
I6 | A | 5/4/2015
I want to convert to
A | B | C
JAN 1 | 1 | 0
FEB 1 | 0 | 0
MAR 0 | 1 | 1
APR 1 | 0 | 0
Where numbers under neath A, B, C are the count that falls in the particular month.
View 3 Replies
View Related
Dec 6, 2013
Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013
Query
SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
(sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime
[Code] ....
Results
Jan-13Feb-13
RPTDESCReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriage
TimeReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriageTime
20506060120252558014014017039394
[Code] ...
Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.
View 2 Replies
View Related
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
Apr 26, 2007
Hi Folks,
Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).
Table Structure
----------------
Employee_Master
ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA
I require the data in the below format..
For the year '2007-2008'
Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10
Thanks.
View 2 Replies
View Related
Apr 11, 2008
I have a query that counts the number of pay checks received in a month, but I need to compare that to the number of actual paydays in a month. If we were on a set pay schedule (i.e. the 15th of each month) it would be easy. Unfortunately that's not the case. We are paid biweekly on Fridays and our last pay day was April 4th.
So I need to know how many pay periods were in a specified month. For example, April would have 2 (April 4th and 18th), May would have 3 (May 2nd, 16th, and 30th), etc.
As always, your help is very much appreciated!
View 10 Replies
View Related
Mar 4, 2007
I am trying to make a query on one table, an invoices table, and Iwant to see how many orders are in each monthYear Month Count---------------------------2006 01 802006 02 1102006 03 208....I cant just do Distinct MonthPart because we have years, so to getthese three columns, is just beyond my SQL skills.Any suggestions or pointer would be greatly appreciated!!!!
View 2 Replies
View Related
Oct 7, 2015
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
View 4 Replies
View Related
Sep 12, 2006
Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime
Sample data:
Id Name AddDate
1 John 01/15/2005
2 Jane 01/18/2005
.
.
.
101 Jack 01/10/2006
102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
from dbo.sampletable
group by datename(mm, AddDate), datepart(yy, AddDate)
order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
View 3 Replies
View Related
Nov 19, 2015
I want to find out count of same UserID used within 7 days for that month.
For example, in below scenario, UserID 458's 1st TTo is 8/26/2015 and it used again on 8/28/2015 which is less than 7 so result should be 1. (DateDiff between 1st TTo and 2nd Tfrom should be less than 7) This is for month of Aug.
ID TFrom TTo
9876 8/1/2015 8/7/2015
4140 8/21/2015 9/4/2015
458 8/23/2015 8/26/2015
458 8/28/2015 9/8/2015
Scenario 2,for UserId 458, TTo is 9/20 and it used again Tfrom on 9/20 so result should be 1
user ID TFrom T To
592 9/1/2015 9/24/2015
526 9/3/2015 9/11/2015
292 9/11/2015 9/25/2015
352 9/12/2015 9/24/2015
458 9/14/2015 9/20/2015
458 9/20/2015 10/2/2015
706 9/22/2015 10/6/2015
View 5 Replies
View Related
Aug 18, 2014
I need developing a query to get the average count by the following:
Day - use daily info for the last ??? days
Weekly - average
- Add all days and divide by 7
- As of Saturday midnight
Monthly - average
- Add all days and divide by days in the month
- As of last save on last day of month
Quarter - average
- Add all days and divide by number of days in the quarter
- As of last day of quarter
Year - average
I don't have requirements for year as of yet.
How can I get the avery count per these timeframes?
View 7 Replies
View Related
Mar 30, 2015
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
View 9 Replies
View Related
Sep 18, 2015
I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..
With Member ProductRank as
Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]))
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]
Output:
It runs for 13 seconds on my laptop. I have modified the query to get the results fast like With
Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])
Member ProductRank as
Rank([Date Due].[Calendar].currentmember,MySet)
Member PrimaryProd as
Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1))
Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]
Output:
This query runs instantly and result is not the same. Am I missing something here?
View 2 Replies
View Related
Sep 17, 2004
Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:
Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...
Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.
View 2 Replies
View Related
Aug 15, 2007
i have a datatable with Part , DateSold,ItemsSold
i want to show the sales for the last 12 months by month. if there were no sales for a given month i want the table to show 0.
here was my first attemp that only gives me data if all twelve months had sales.
SELECT MONTH(DateSold) AS Month, SUM(ItemsSold) AS ThisYear
FROM inv_Monthly_Sales
WHERE (YEAR(DateSold) = @Yr - 1) AND (Part = @Part)
GROUP BY MONTH(DateSold)
ORDER BY Month
i tried using isnull with the sum but that didn't work.
how can i force the table to always generate a dummy table of 12 months and then left join to it and in that way force it to give 12 moths of sales even when it does'n exist.
your help is greatly appreciated,
slimshim
View 15 Replies
View Related
Nov 9, 2014
This is my Statement.
SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
a.PFGLTAB,
[Code] .....
But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time. How can i select dynamically all row between 01.10 and 31.10 ?
FIRMAPSNRFELDNRPFLFDNRCardnrPFGLTAB PFGLTBISKSTNRPSPERSNRPSVORNAPSNACHNA
13980211DU9572014111220321231000043000957xxxxx xxxxxx
13990211DU9552014110120321231000010000955xxxxx xxxx
14030211DU9602014120120321231000086000960xxxxxxxxx
View 10 Replies
View Related
Apr 5, 2008
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
View 3 Replies
View Related
Apr 5, 2001
I have a table called Cartype and there is field called typeid contained 5 rows such as A,B,D,C,E,F
Ihave another table called transaction that the field called typeid and the data contains 3 rows of A and 3 rows of B , and 1 rows of E
I want to write the sql that output something like this
A 3
B 3
D 0
E 1
F 0
thanks
View 1 Replies
View Related
Mar 13, 2008
Is it possible to count total no. of rows in a table without scanning whole table??
View 10 Replies
View Related
Sep 25, 2007
Hi,
I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.
I want to do the following -
Sum the total no. of rows in mdb while the package executes
Sum the total no of rows transferred to Sql server.
Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)
Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.
I saw a RowCount transformation..but just dont know where to place and what to do with it.
thanks
View 4 Replies
View Related
Jan 4, 2007
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid.
I am not quite sure how to do this.
Any ideas?
~mike~
View 5 Replies
View Related
Dec 20, 2007
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
View 8 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
Mar 1, 2001
Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.
View 2 Replies
View Related
Dec 29, 2006
I'm new to MS SQL having used MySQL for several years now. I just can't figure out the syntax on counting or selecting rows
a) in the Last X (days, weeks, months...)
or
b) between "date X" and "date Y"
I've tried this to get the last week:
SELECT COUNT(*)
FROM contacts
WHERE CONVERT(datetime,timestamp) < (GETDATE() - DATEADD(DAY,7,GETDATE()))
...but I always get a total that includes rows outside the span.
Any suggestions? Thanks!
View 7 Replies
View Related
Nov 11, 2005
hey, how would i count the number of rows, with out using a loop??
thanks, Justin
View 2 Replies
View Related
Oct 23, 2013
The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
View 4 Replies
View Related
May 30, 2008
I need to count how many rows are inserted and then how many updated from a table then put that result in a new table.
Lisa Jefferson
View 6 Replies
View Related
Jun 21, 2014
This is my sql string. It counts all the rows in Questions table but it should only count the rows where id in Quizzes matches the quiz column in Questions table.
"select Quizzes.name, Quizzes.id, count(Questions.quiz) as total from Quizzes inner join Questions on Quizzes.id=Questions.quiz"
Why isnt it doing what I want it to do?
View 4 Replies
View Related
Oct 30, 2007
Hello,
I am doing a SSIS package to transfer rows from access db to sql server using foreach file. Before loading the data, I want to count the number of rows in a table of the access db. How do I do that?
thank you in advance
View 4 Replies
View Related
Jul 23, 2005
i have a table like thisStoreId DateClicked1 1/1/20042 1/1/20041 1/1/20041 1/1/20041 1/1/20043 1/1/20042 1/1/2004I want the result of the query to be this:NumberOfClicks StoreId4 12 21 3what is the syntax for this? Thanks.
View 1 Replies
View Related