spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

SQL Server 2005 GROUP BY and COUNT query for each month


This can be written much simpler by using a CASE expression inside of an aggregate function. This process is called PIVOT:

select book,
  sum(case when month(Date) = 1 then 1 else 0 end)
Jan,
  sum(case when month(Date) = 2 then 1 else 0 end)
Feb,
  sum(case when month(Date) = 3 then 1 else 0 end)
Mar,
  sum(case when month(Date) = 4 then 1 else 0 end)
Apr,
  sum(case when month(Date) = 5 then 1 else 0 end)
May,
  sum(case when month(Date) = 6 then 1 else 0 end)
Jun,
  sum(case when month(Date) = 7 then 1 else 0 end)
Jul,
  sum(case when month(Date) = 8 then 1 else 0 end)
Aug,
  sum(case when month(Date) = 9 then 1 else 0 end)
Sep,
  sum(case when month(Date) = 10 then 1 else 0
end) Oct,
  sum(case when month(Date) = 11 then 1 else 0
end) Nov,
  sum(case when month(Date) = 12 then 1 else 0
end) Dec
from Rentals
where year(date) = 2011
group by book;

See SQL Fiddle with Demo. Instead of querying the table multiple times for each column, you use conditional aggregation to get the count for each book during the month and year.


Categories : SQL

Related to : SQL Server 2005 GROUP BY and COUNT query for each month
Query that will convert date into Month with count
You could do this: SELECT COUNT(*) AS [Count], DATENAME(month,table1.[Received Date]) AS [Month] FROM table1 GROUP BY DATENAME(month,table1.[Received Date]) Update Some of the date can't be converted to a datetime. If you know that the varchar will always have the same format. You can use convert like this: SELECT COUNT(*) AS [Count], DATENAME(month,CONVERT(datetime,

Categories : SQL
LINQ query with group joins and count needs refactoring
Help methods: int LikeCount(DbLikes likes, int typeId, int type, int likeType) { return likes.Count(like => like.TypeId == typeId && like.Type == type && like.LikeType == likeType); } Help variables: var commentType = Model.PostType.Comment.ToString(); var postType = Model.PostType.Post.ToString(); var likeType = (int)Model.LikeType.Like; var dislikeType = (

Categories : C#
how to group a list of dates by year and then by month?
You can add further groups by adding closures to the groupBy call. Calendar's MONTH is off by one (starts at 0), so add one there. Next you want to fill all month with the count, which is the size() of the group by results. This will be collected over 1..12 for each month (proper order and null for the missing groups). def date = new Date() def dates = [date-350, date-400, date-300, date-50, d

Categories : Groovy
group by year, month with 2 datefields conditions
First, create a table that contains all the months you care about: CREATE TABLE months ( year INTEGER, month INTEGER, start DATE, end DATE, PRIMARY KEY (year, month) ); INSERT INTO months VALUES (2014, 1, '2014-01-01', '2014-01-31'), (2014, 2, '2014-02-01', '2014-02-28'), (2014, 3, '2014-03-01', '2014-03-31'), ...; Then join this table with your event table: SELECT m.year,

Categories : Mysql
First business day of the current month - SQL Server
If you have more flexibility in your SELECT statement, you might be able to use something like this: ;With Daterange As ( Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) As Date Union All Select DateAdd(Day, 1, Date) As Date From DateRange Where Date < DateAdd(Day, 6, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)) ) Select Convert(Date, Min(Date)) FirstBusine

Categories : SQL
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.