Board index » delphi » SQL Group by Year Month

SQL Group by Year Month

Please advice what is the select statement for query,
which totalize sales_order_amount base on or group by Year and Month of
Sales_Date

SELECT EXTRACT(year from sales_date), EXTRACT(month from sales_date),
SUM(sales_order_amount)
FROM sales_order
GROUP BY EXTRACT(year from sales_date), EXTRACT(month from sales_date)

I have tried the following SQL nut it did not like it, please help.

Thank in advance.

 

Re:SQL Group by Year Month


Please advice what is the select statement for query,
which totalize sales_order_amount base on or group by Year and Month of
Sales_Date

SELECT EXTRACT(year from sales_date), EXTRACT(month from sales_date),
SUM(sales_order_amount)
FROM sales_order
GROUP BY EXTRACT(year from sales_date), EXTRACT(month from sales_date)

I have tried the following SQL nut it did not like it, please help.

Thank in advance.

Re:SQL Group by Year Month


You can do that with:

a) Creating and querying a view or
b) Add COMPUTED BY columns for the extracted year and month to your table
sales_order

ad a)

CREATE VIEW SALES_ORDER_VIEW (
  SALES_DATE_YEAR,
  SALES_DATE_MONTH,
  SALES_ORDER_AMOUNT
) AS
SELECT
  EXTRACT(YEAR FROM SALES_DATE),
  EXTRACT(MONTH FROM SALES_DATE),
  SALES_ORDER_AMOUNT
FROM SALES_ORDER;

Then you can query the view like:

select sales_date_year, sales_date_month, sum(sales_order_amount)
from sales_order_view
group by sales_date_year, sales_date_month;

ad b)

Add 2 COMPUTED BY columns to your table. For example:

ALTER TABLE SALES_ORDER ADD SALES_DATE_YEAR COMPUTED BY (EXTRACT(YEAR FROM
SALES_DATE));
ALTER TABLE SALES_ORDER ADD SALES_DATE_MONTH COMPUTED BY (EXTRACT(MONTH FROM
SALES_DATE));

Then query your table with:

select sales_date_year, sales_date_month, sum(sales_order_amount)
from sales_order
group by sales_date_year, sales_date_month;

HTH,
Thomas Steinmaurer
IB LogManager 2.0 - The Logging/Auditing Tool for InterBase and Firebird
http://www.iblogmanager.com

"Eddy" <E...@no-spam.com> schrieb im Newsbeitrag news:3d0db107_2@dnews...

Quote
> Please advice what is the select statement for query,
> which totalize sales_order_amount base on or group by Year and Month of
> Sales_Date

> SELECT EXTRACT(year from sales_date), EXTRACT(month from sales_date),
> SUM(sales_order_amount)
> FROM sales_order
> GROUP BY EXTRACT(year from sales_date), EXTRACT(month from sales_date)

> I have tried the following SQL nut it did not like it, please help.

> Thank in advance.

Re:SQL Group by Year Month


ALTER TABLE SALES ORDER
ADD SALES_DAY COMPUTED BY (EXTRACT(DAY from SALESDATE));

ALTER TABLE SALES ORDER
ADD SALES_MONTH COMPUTED BY (EXTRACT(MONTH from SALESDATE));

 SELECT sales_day, sales_month,SUM(sales_order_amount)
 FROM sales_order
 GROUP BY sales_day,sales_month

or you can create stored procedure or views. Your not using  GROUP BY with
Cast(),Extract(),Udf ....

Quote
----- Original Message -----
From: "Eddy" <E...@no-spam.com>

Newsgroups: borland.public.interbase.general
Sent: Monday, June 17, 2002 12:53 PM
Subject: SQL Group by Year Month

> Please advice what is the select statement for query,
> which totalize sales_order_amount base on or group by Year and Month of
> Sales_Date

> SELECT EXTRACT(year from sales_date), EXTRACT(month from sales_date),
> SUM(sales_order_amount)
> FROM sales_order
> GROUP BY EXTRACT(year from sales_date), EXTRACT(month from sales_date)

> I have tried the following SQL nut it did not like it, please help.

> Thank in advance.

Re:SQL Group by Year Month


Thank you very much for your help folk
My problem solved.
Quote
"Eddy" <E...@no-spam.com> wrote in message news:3d0db107_2@dnews...
> Please advice what is the select statement for query,
> which totalize sales_order_amount base on or group by Year and Month of
> Sales_Date

> SELECT EXTRACT(year from sales_date), EXTRACT(month from sales_date),
> SUM(sales_order_amount)
> FROM sales_order
> GROUP BY EXTRACT(year from sales_date), EXTRACT(month from sales_date)

> I have tried the following SQL nut it did not like it, please help.

> Thank in advance.

Other Threads