group by clause,mysql query on data

[rajuchacha007]

Hi,

I am using mysql query to generate a report. I would like to know can whether we can use group by clause on data itself? Let me elaborate. Let'ss asssume I have following table called employee and database is myrecords.

--------------------------------------------------------------------------------------------------------------------------------------------------------------

id             name                             accountno                               city                                     descrption                        sex

--------------------------------------------------------------------------------------------------------------------------------------------------------------

1              abc                                 100                                           NY                                     senior executive             m

2              pqr                                  200                                           NY                                     senior executive             f

3              xyz                                   300                                           NY                                     senior executive             m

4              stu                                  400                                           LON                                   technical expert              f

5              def                                   500                                          PAR                                   senior executive              m

6             rth                                      600                                                                                    HR                                      m

7             uty                                      700                                                                                    HR                                      f

8             ytt                                        800                                                                                   technical expert 

---------------------------------------------------------------------------------------------------------------------------------------------------

 

Now I want to display the records based on the description. Partiularly, I am looking for the data based on description only  "senior executive" and "HR". I do not wish to take any other records. In short, I am looking for the data based on the data. Can I extract data from data? Therefore, I am using group by description clause. How can I use it?

select employee.id,employee.name,employee.accountno,employee.city,employee.description

from employee group by employee.description order by employee.id

Any suggestions or ideas friends?

 

Best regards.

[slow]

uhm... I  think I don't fully understand your problem because the solution seems too simple...

why don't you use the HAVING clause (see mysql syntax)
http://www.databasejournal.com/features/mysql/article.php/3469351/The-HAVING-and-GROUP-BY-SQL-clauses.htm

another way is to use the jasper report (or IReport :) ) filter expression...

 

are you sure your query is correct?
reading it:

select employee.id,employee.name,employee.accountno,employee.city,employee.description
from employee
group by employee.description
order by employee.id

I think you are in confusion about the use og Group in SQL syntax and Group in IReport...
from wikipedia:

• The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
• The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.

if you select all details of a single employee... i think without aggregation functions GROUP BY hasn't effect.

your query can be:

select employee.id,employee.name,employee.accountno,employee.city,employee.description
from employee
where employee.description = 'senior executive' OR employee.description = 'HR'
order by employee.id

 

____________________________________
if it works... give me KARMA points please!    : ) 

 

 

 

Post Edited by slow at 04/22/2010 08:08

[rajuchacha007]

Your solution is right. I showed me some positive direction although it didn't work in my example. Karma is given.

 

Thank and best regards.

[rajuchacha007]

Will you please let me know any example of filter expression in ireport?

[slow]

A Filter Expression is a Boolean returning expression applied to each detail-row of your report...

if the row satisfy the expression (returning new Boolean(True)) then it's printed.

suppose you have a query like this (it's a dummy query!)
select 1 as val from dual
union
select 2 as val from dual
union
select 3 as val from dual
union
select 4 as val from dual
union
select 5 as val from dual
union
select 6 as val from dual
union
select 7 as val from dual

and you create a filter expression like:
new Boolean($F{VAL}.intValue()>5)

then the result contains olny two rows... val=6 and val=7...

it is similar to set a WHERE condition in a SQL statement (that i prefer...)
(where val>5)

It can be edited from report->edit query->filter expression

 

____________________________________
if it works... give me KARMA points please!    : ) 

Post Edited by slow at 04/22/2010 10:14

[rajuchacha007]

Hey Thanks....I am using ireport 3.7.1. Have you got any documentation about this particular edition?

[slow]

NO , sorry...  :)

I use a very old IReport version due an old inherited application... :) but it works fine.

 

 

____________________________________
if it works... give me KARMA points please!    : )