Solved

Need to group results by quarter (not month)

Posted on 2006-11-29
1
424 Views
Last Modified: 2008-02-01
Hi
I have a SQL statement that extracts sales by quarter, problem is I need to use the date field as part of the select query, and since there is an aggregation in the query, my output get grouped by month as opposed to by quarter ie; I get 3 lines for each quarter where  I only want 1 line - any ideas?
thanks
Fergal

Query:

select  'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2) as sales_qtr,
case when geo='EU' then 'EMEA' when geo='AP' then 'APAC' when geo='AM' then 'AMO' end as geo,bgrp_nm, div_dscr, op_id,
SUM(net_blng_amt) from net_gross_sales_new
where op_id = 'BM' and intel_date >='01-01-2006' and geo = 'AP'
group by intel_date, geo,bgrp_nm, div_dscr, op_id
order by sales_qtr

Output:
Q1'06      APAC      Business Client Group      UPSD Division      BM      21720246.9100
Q1'06      APAC      Business Client Group      UPSD Division      BM      13782960.7000
Q1'06      APAC      Business Client Group      UPSD Division      BM      14164670.0600
Q2'06      APAC      Business Client Group      UPSD Division      BM      19134103.3100
Q2'06      APAC      Business Client Group      UPSD Division      BM      23659180.3400
Q2'06      APAC      Business Client Group      UPSD Division      BM      8435456.8200
Q3'06      APAC      Business Client Group      UPSD Division      BM      13244725.7300
Q3'06      APAC      Business Client Group      UPSD Division      BM      5082319.2900
Q3'06      APAC      Business Client Group      UPSD Division      BM      20983776.3700
Q4'06      APAC      Business Client Group      UPSD Division      BM      11132107.9000
Q4'06      APAC      Business Client Group      UPSD Division      BM      11751375.8500

0
Comment
Question by:fjkilken
1 Comment
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 18035761
Hi fjkilken,

Try:

select  'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2) as sales_qtr,
case when geo='EU' then 'EMEA' when geo='AP' then 'APAC' when geo='AM' then 'AMO' end as geo,bgrp_nm, div_dscr, op_id,
SUM(net_blng_amt) from net_gross_sales_new
where op_id = 'BM' and intel_date >='01-01-2006' and geo = 'AP'
group by 'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2), geo,bgrp_nm, div_dscr, op_id
order by sales_qtr

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question