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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.