logo
down
shadow

Query to return month-wise count in PostgreSQL


Query to return month-wise count in PostgreSQL

By : Fighter
Date : October 22 2020, 08:10 PM
I think the issue was by ths following , I am not quiet sure but maybe you are searching for GROUP BY date_trunc():
demo:db<>fiddle
code :
SELECT 
    date_trunc('month', sdate)::date as month_begin,
    (date_trunc('month', sdate) + interval '1 month -1 day')::date as month_end,
    SUM(value)
FROM sales    
WHERE sdate BETWEEN <start> and <end>
GROUP BY date_trunc('month', sdate)


Share : facebook icon twitter icon
Query count with multiple table group by month return zero if month is null

Query count with multiple table group by month return zero if month is null


By : Shubham Gaur
Date : March 29 2020, 07:55 AM
help you fix your problem The WITH block defines a virtual table of months (or rather, the first days of each month) required. This extends your CROSS JOIN to give you all the months.
code :
;WITH months(startdate) AS (
     SELECT CAST('20120901' AS date)
  UNION ALL
     SELECT dateadd(m,1,startdate)
       FROM months
      WHERE startdate < '20121001'
)

    SELECT r.region,
           pt.projectType,
           count(p.id) totalCount,
           DATENAME(Month,m.startdate) monthCount
      FROM region r
CROSS JOIN ProjectTypes pt
CROSS JOIN months m
 LEFT JOIN projects p ON p.regionID = r.id
       AND pt.id = p.TypeID
       AND p.postedOn >= m.startdate
       AND p.postedOn <  dateadd(m,1,m.startdate)
  GROUP BY r.region, pt.projectType, m.startdate
  ORDER BY m.startdate, region, projecttype
    OPTION (maxrecursion 0);
CREATE TABLE Months (
  startdate datetime -- the first day of month
    primary key
);
insert Months
select DateAdd(M,Number,'19990101')
from master..spt_values
where type='P'
GO
    SELECT r.region,
           pt.projectType,
           count(p.id) totalCount,
           DATENAME(Month,m.startdate) monthCount
      FROM region r
CROSS JOIN ProjectTypes pt
      JOIN months m on m.startdate between '20120901' and '20121001'
 LEFT JOIN projects p ON p.regionID = r.id
       AND pt.id = p.TypeID
       AND p.postedOn >= m.startdate
       AND p.postedOn <  dateadd(m,1,m.startdate)
  GROUP BY r.region, pt.projectType, m.startdate
  ORDER BY m.startdate, region, projecttype;
Oracle query to get month wise data and give 0 for month not available

Oracle query to get month wise data and give 0 for month not available


By : Ema Ghanam
Date : March 29 2020, 07:55 AM
will help you You should Join this original table with table with all months in given period. If it is inside one year then we need 1,2,3,...12 sequence.
code :
select 
 listagg(count(Name),',') within 
    group (order by m.rn) 
from 
     (SELECT * FROM  ps_bqueues_host 
      where time_stamp 
        BETWEEN TO_DATE('01-Mar-14', 'DD-Mon-YY') 
        and     TO_DATE('01-Jun-14', 'DD-Mon-YY') 
     )   
RIGHT JOIN 
     (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 12) m
     ON m.rn=EXTRACT(month FROM time_stamp)

WHERE m.rn BETWEEN EXTRACT(month FROM TO_DATE('01-Mar-14', 'DD-Mon-YY'))
           AND  EXTRACT(month FROM TO_DATE('01-Jun-14', 'DD-Mon-YY'))
GROUP BY m.rn
sql query to get month wise count

sql query to get month wise count


By : user3609763
Date : March 29 2020, 07:55 AM
should help you out below is my data present in my database. , Below Query will help you :
code :
SELECT
  MONTH     ,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 1, 1, 0))  AS Jan,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 2, 1, 0))  AS Feb,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 3, 1, 0))  AS Mar,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 4, 1, 0))  AS Apr,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 5, 1, 0))  AS May,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 6, 1, 0))  AS Jun,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 7, 1, 0))  AS Jul,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 8, 1, 0))  AS Aug,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 9, 1, 0))  AS Sep,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 10, 1, 0)) AS OCT,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 11, 1, 0)) AS Nov,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 12, 1, 0)) AS `Dec`
  ,COUNT(*) totalCoupon
  ,SUM(price) totalprice

FROM mytable
GROUP BY MONTH     ;
SQL query to get the count based on month wise

SQL query to get the count based on month wise


By : S. Mc
Date : March 29 2020, 07:55 AM
this one helps. This is login table where I need to display username with count they have logged in to application and split it by month wise the data to show the count based on month wise
code :
;WITH ctePrepared AS (
    SELECT
       UserName
       ,TotalCount = SUM([Count]) OVER (PARTITION BY UserName)
       ,[MonthName] = DATENAME(MONTH,[Datetime])
       ,[Count]
    FROM
       @Table
)
ORDER BY
    UserName DESC

SELECT *
FROM
    ctePrepared t
    PIVOT
    (
       SUM([Count])
       FOR [MonthName] IN (January,February,March,April,May,June,July,August,September,October,November,December) 
    ) pvt
DECLARE @Table AS TABLE (UserName VARCHAR(50), [Count] INT, [Datetime] DATETIME)
INSERT INTO @Table
VALUES ('Test-1',1,'2017-06-02')
,('Test-1',1,'2017-07-20')
,('Test-1',1,'2017-09-20')
,('Test-1',1,'2017-09-20')
,('Test-1',1,'2017-09-15')
,('Test-1',1,'2017-10-22')
,('Test-1',1,'2017-10-23')
,('Test-2',1,'2017-06-12')
,('Test-2',1,'2017-06-12')
,('Test-2',1,'2017-07-24')
,('Test-2',1,'2017-09-23')
,('Test-2',1,'2017-09-29')
,('Test-2',1,'2017-09-11')
,('Test-2',1,'2017-10-12')
,('Test-2',1,'2017-10-19')
,('Test-2',1,'2017-10-21')
Return Month wise count if no data for month return 0 as count in oracle sql

Return Month wise count if no data for month return 0 as count in oracle sql


By : akkyy
Date : March 29 2020, 07:55 AM
I wish this help you I have a table having data for January to March (till current month) and I am able to take the month wise count.But user required is to display zero for rest of the month.Kindly suggest. , Right join months generator with your query:
code :
select to_char(to_date(mth_num, 'MM'), 'MONTH') month, nvl(cnt, 0) cnt
  from (
    select count(emp_id) as cnt, to_char(due_date, 'mm') mth_num
      from emp_request where due_date is not null
      group by to_char(due_date, 'mm')) e
  right join (
    select to_char(level, 'fm00') mth_num 
      from dual connect by level <= 12) m using (mth_num)
  order by mth_num
select to_char(level, 'fm00') mth_num from dual connect by level <= 12
select to_char(rownum, 'fm00') mth_num from all_objects where rownum <= 12
select to_char(column_value, 'fm00') mth_num 
  from table(sys.odcivarchar2list(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
select to_char(sysdate, 'month', 'nls_date_language=english') from dual
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org