Link to home
Start Free TrialLog in
Avatar of fjkilken
fjkilken

asked on

Need to group results by quarter (not month)

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

ASKER CERTIFIED SOLUTION
Avatar of adathelad
adathelad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial