GROUP BY - not working for me

[evild]

I do lack the experience of SQL programming so it may be an easy question for the xperts to answer

I have a query that is returning a list and I want to use the group by clause. However I cannot get it to work. I get error messages saying that it is incorrectly formatted or does not recognise the command.

here is the query...

SELECT z68_order_date,
substr(z68_order_number,1,15),
substr(z13_author,1,20),
substr(z13_title,1,35),
z13_isbn_issn
FROM z68, z13
WHERE substr(z68_rec_key,1,9) = z13_rec_key
AND z68_order_status = 'SV'
AND z68_order_type = 'M'
AND z68_order_date <= '20030408'
group by z68_sub_library;

Now, for using the group by statment do I have to include the field in the select clause?
I dont think the group by would work on this query so I would like some advice on how to do so.
Basically I wan the results listed by z68_sub_library (there are only three different types).
How would I change this query to do so?

I should say that there are two tables and I'm matching by rec_key, which is the primary key for both tables.

 

[swampBoogie]

Why do you want to use group by?

Give some sample data and the expected result.

 

[JeanNiBee]

Hi

GROUP by is used ONLY when you utilize aggregate functions like SUM(), MIN(), MAX(), COUNT(), AVG() etc. In your case you want to ORDER BY.

Ex:
SELECT
z68_order_date, substr(z68_order_number,1,15),
substr(z13_author,1,20), substr(z13_title,1,35),
z13_isbn_issn
FROM
z68, z13
WHERE
substr(z68_rec_key,1,9) = z13_rec_key
AND
z68_order_status = 'SV'
AND
z68_order_type = 'M'
AND
z68_order_date <= '20030408'
ORDER BY
z68_sub_library ASC

 

[BillDHS]

Group By can also be used to eliminate duplicate values: for example if I have a 1000 row table with a gender column if I select only that column, I will get 1000 F's and M's if I Group By that column, I will get 1 F and 1 M.

When you use Group By you MUST identify ALL non-aggregate columns in the Clause. In the instance above, I don't know how you can group by a column not in your select.

 

[JeanNiBee]

Hi

Yes, that would work if the only column you select was the GROUPED BY column. Once you add other columns to the 'select' clause you won't see any benefits nor do you see grouping unless you add aggregate functions.

In addition, if you really need a listing like you mentioned, you can use (and I'm sure you know about this) the DISTINCT function.

I hope evild responds to this thread as I would like to understand 'why' he wants to group, maybe this will make it easier for to help him/her out.

TTYL.
-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

TGML reference:

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450&quot;

]