how to work with group by query

himanshu

Hi,

Suggest how we can work with the group by like queries, like i having a table

of customer in which i want to group by all customer by country.

or we get summary of something number of customers from a country...

tford

Sorry, I'm having a really hard time understanding the English.

Can you clarify?

Thanks,
Tom

himanshu

sorry, i think needs to describe the issue ..so here it is

If u are familar with the SQL (Structure Query Language) then u also know about the GROUP BY clause QUERIES.

LIKE "SELECT CUSTOMER.NAME, SUM(ORDERQTY) FROM SALES GROUP BY CUSTOMER.NAME "

SO WE GOT RESULTS OF DISTINCT CUSTOMER AND TOTAL NUMBER OF ORDER QTY.

so i am just looking the way to perform these type of queries.

i think now English and the subject is clear. 8-)

tford

Himanshu,

Thanks for the clarification.

It depends on where you want to use the number of orders by customer. If you want to use it in a Report or a Custom Presentation of a BO, you would just use report bands & the COUNT function.

If you need the information on a query or form, you will have to create a new BO, let say CustomerSummary to accumulate the summary info. That summarized info could then be displayed.

Tom

himanshu

Thanks Buddy,

as u right with the solution, and i also did little work on it also.
but can u suggest me something for the following what i am looking for...

LIKE WE HAVE A CUSTOMER TABLE AND IN THAT THERE ARE THE FOLLOWING ATTRIBUTES

NAME
ADDRESS
COUNTRY

NOW I LIKE TO USE PRESENTATION AND IN THAT CHART I LIKE TO PRESENT THE DATA.
I CREATED A GROUP BAND OF CUSTOMER.COUNTRY AND WANT TO SHOW NUMBER OF CUSTOMER IN DISTINCT COUNTRY....

NORMALLY WE CAN DO BY
COUNT FUNCTION..[LIKE COUNT CUSTOMER WHERE CUSTOMER.COUNTRY='USA']

BUT ABOVE IN THE VALUE IS FIXED TO USA AND WE WANT TO COUNT IN ALL THE DISTINCT COUNTRY'S STORE IN THE CUSTOMER TABLE.

IN sql we do like "select country, count(*) from customer group by country".

please help me how to do in AwareIM

tford

There is a COUNT function -- see the User Guide for more info.

The Query that drives the Presentation will determine which Countries are shown in the presentation.

The COUNT function will count up all items for that band.

Tom

himanshu

Hi Tom,

I read and implement COUNT function successfully at many places, but like taking the same above example of customer if i want to show data on chart then COUNT function not works or solve my problem.

we insert a chart in the report,

on X axis
CUSTOMER.COUNTRY

on Y axis
? (here i want data count number of customer from country)

we have a group band of CUSTOMER.COUNTRY already declared

here is the some dummy data

NAME ADDRESS COUNTRY
XYZ JIJI USA
SSS JIJIJ JAPAN
ABC KKO USA
KISJ KSKS INDIA
ABH KKO USA
KIM KSKS INDIA

so results or chart show this...

on X axis USA JAPAN INDIA

on Y axis 3 1 2

tford

What band are you placing the COUNT function and the chart in?

Tom

himanshu

I tried in both Tittle and Summary Panel

tford

Sorry but I think you are going to have to create a new BO, let say CustomerSummary to accumulate the summary info. That summarized info could then be displayed.

Tom