what if I want more fields than in the group by 1

[RSX02]

I have a SELECT with a group by. I saw that I have to put each field in the select in the group by. But what if I need more field in my select that I don't want to group by them?
for example I have this code and I would like to add the field co_num in the select without to group by it.

SELECT co_ship.ship_date, coitem.item, accum = sum(coitem.qty_shipped)
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country
group by co_ship.ship_date, coitem.item

Thanks in advance

 

[SQLSister]

Maybe it would be best to give us some sample data and a sample of what results you want returned. You can't add a field to this query without adding it to the group by, so you need a different approach to what you want. To figure the proper approach I need to see what your data looks like and what results you want from it.

 

[RSX02]

ok
what I need to do is cumulate the field qty_shipped for each record that the ship_date and the Item are the same even if it is in different customer order (co)

for example,

co_num Item.item co_ship.Ship_date coitem.qty_shipped
10 cp-10000 2002-10-26 2
11 cp-13000 2002-10-26 4
12 cp-10000 2002-11-24 7
13 cp-10000 2002-10-26 6

it should group the line of the co_num 10 and the line of the co_num 13 to make a total of coitem.qty_shipped that should give 8.
The point is that I need to select other field in my querie. You told me that it's not possible.

So Here is the code that I have that I would like to merge both together. (for having all the fields in the select and to be able to group it by ship_date and by item.)

To resume: I need to accumulate the qty shipped for each line of my co that the ship_date and the item are equal. So I'll have a total of each item that was ship this day instead of see each time that this item was shipped this day.

SELECT co_ship.[co_num], co_ship.ship_date, coitem.co_line ,coitem.qty_shipped ,item.item,item.description , item.uf_country_of_origin,item.uf_int_export, item.uf_us_export,
(SELECT TOP 1 itemprice.unit_price1 FROM itemprice WHERE itemprice.item = coitem.item AND itemprice.curr_code = 'CDN' AND effect_date <= GETDATE() ORDER BY effect_date DESC) as most_recent_price
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
INNER JOIN itemprice
ON (itemprice.item=coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country


this one do the group by

SELECT co_ship.[co_num],co_ship.ship_date, coitem.item, accum = sum(coitem.qty_shipped)
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country
group by co_ship.ship_date, coitem.item

Thanks in advance

 

[jiqjaq]

The situation of needing to list more columns in the SELECT that aren't wanted in the GROUP BY is a very common one. The standard way of dealing with this is to use MAX(non_grouped_field) rather than just non_grouped_field.

The use of an aggregate function like MAX is always allowed for grouping, just like your use of SUM. The MAX will be done separately on each individual record, rather than on a group, since the field is not actually part of a group. Therefore MAX(fieldX) will be the same as fieldX in this case.

When you don't use any groups, the aggregate function will work against the entire table. When you do use groups, it works against the whole group if the field is listed in the GROUP BY, or only against the record itself when it's not in the GROUP BY. (I hope that made sense.)

So you could write:
SELECT co_ship.ship_date, coitem.item, accum = sum(coitem.qty_shipped), MAX(co_num)

 

[plantj]

RSX02

jiqjaq has told you no lies but I'm not sure either of us understands your requirements yet

You said that given this data...

co_num Item.item co_ship.Ship_date coitem.qty_shipped
10 cp-10000 2002-10-26 2
11 cp-13000 2002-10-26 4
12 cp-10000 2002-11-24 7
13 cp-10000 2002-10-26 6

You will want
cp-10000 2002-10-26 8

But what about the co_num that you want in the result set? Do you want the smallest co_num (10); the largest (13); or, both which would give...

10 cp-10000 2002-10-26 8
13 cp-10000 2002-10-26 8

And I think once you can explain this you can use the clues jiqjaq has given to produce the result you need.

 

[RSX02]

plantj
It should give :
1st group
10 cp-10000 2002-10-26 8
13 cp-10000 2002-10-26 8
Note: 8 is the total of the qty shipped for this group (2 + 6)

2nd group
12 cp-10000 2002-11-24 7

3rd group
11 cp-13000 2002-10-26 4

I just want to group my data by record that have the same ship date and item. After that, I want to accumulate the qty shipped by group.
Note: I have to select more fields than I have in the group.
Example:
SELECT co_ship.[co_num], co_ship.ship_date, coitem.co_line ,coitem.qty_shipped ,item.item,item.description , item.uf_country_of_origin,item.uf_int_export, item.uf_us_export,
(SELECT TOP 1 itemprice.unit_price1 FROM itemprice WHERE itemprice.item = coitem.item AND itemprice.curr_code = 'CDN' AND effect_date <= GETDATE() ORDER BY effect_date DESC) as most_recent_price

thanks in advance again.

 

[RSX02]

I tried that, but it doesn't allow me to see each field that has the &quot;max&quot; aggregate function in crystal report. As if my stored procedure doesn't contain any of these fields. Please I really need to make it works.

SELECT max(co_ship.[co_num]), co_ship.ship_date, max(coitem.co_line) , max(coitem.qty_shipped) , max(item.u_m), max(item.item), max(item.description) , max(item.uf_country_of_origin), max(item.uf_int_export), max(item.uf_us_export), accum = sum(coitem.qty_shipped),
(SELECT TOP 1 itemprice.unit_price1 FROM itemprice WHERE itemprice.item = coitem.item AND itemprice.curr_code = 'USD' AND effect_date <= GETDATE() ORDER BY effect_date DESC) as most_recent_price
FROM co_ship
INNER JOIN coitem
ON (coitem.co_num = co_ship.co_num and coitem.co_line = co_ship.co_line)
INNER JOIN item
ON (item.item = coitem.item)
INNER JOIN itemprice
ON (itemprice.item=coitem.item)
where co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country
group by co_ship.ship_date, coitem.item

 

[JamesLean]

Let's take your example resultset:

co_num | item | ship_date | qty_shipped
10 | cp-10000 | 2002-10-26 | 2
11 | cp-13000 | 2002-10-26 | 4
12 | cp-10000 | 2002-11-24 | 7
13 | cp-10000 | 2002-10-26 | 6

I would set the SELECT statement that gave you that result as a view, let's say myview. Now to get the result you want, you can use:

SELECT
  co_num,
  item,
  ship_date,
  (SELECT SUM(qty_shipped) FROM myview
  WHERE item = v1.item AND ship_date = v1.ship_date) AS sum_qty_shipped
FROM myview v1
ORDER BY item, ship_date

--James

 

[RSX02]

James
First, Thanks for your time.
Second, I'm not sure that I understand about the view. Do I have to create another select that will be called myview? If so, what do I have to put in it.
Sorry about my ignorance but I never heard about view in SQL since I'm beginner with it.
Thanks again.

 

[JamesLean]

A view is basically a saved SELECT statement which you can access just like a normal table, eg:

CREATE VIEW myview
AS

SELECT col1, col2, col5
FROM table1
WHERE col3 = 'abc'
GO

SELECT * FROM myview

So what you could do is take the SELECT statement that gave you your sample resultset and put that into the view. Then run my query against it.

You can look up views in BOL for more info.

--James

 

[RSX02]

Thanks James
That's seem to make sense.
I have another little question question. In myview I have to put some parameters. How can I send the value of the parameters in myview?

This is myview
CREATE VIEW dbo.mods_myview
AS
SELECT dbo.co_ship.co_num, dbo.co_ship.ship_date, dbo.coitem.co_line, dbo.coitem.qty_shipped, dbo.item.item, dbo.item.description,
dbo.item.uf_country_of_origin, dbo.item.uf_int_export, dbo.item.uf_us_export,
(SELECT TOP 1 itemprice.unit_price1
FROM itemprice
WHERE itemprice.item = coitem.item AND itemprice.curr_code = 'CDN' AND effect_date <= GETDATE()
ORDER BY effect_date DESC) AS most_recent_price
FROM dbo.co_ship INNER JOIN
dbo.coitem ON dbo.coitem.co_num = dbo.co_ship.co_num AND dbo.coitem.co_line = dbo.co_ship.co_line INNER JOIN
dbo.item ON dbo.item.item = dbo.coitem.item INNER JOIN
dbo.itemprice ON dbo.itemprice.item = dbo.coitem.item
/*WHERE co_ship.ship_date = @shippingdate
AND item.uf_country_of_origin = @country*/

And this is my SP

CREATE procedure mods_rpt_ExportDeclaration(
@shippingdate datetype = null,
@country countrytype = null,
@accum as qtyunittype output
)
as
SELECT
co_num,
item,
ship_date,
(SELECT SUM(qty_shipped) FROM myview
WHERE item = v1.item AND ship_date = v1.ship_date) AS sum_qty_shipped
FROM myview v1
ORDER BY item, ship_date
GO

Thank you

 

[JamesLean]

Well, you can't parameterise a view unfortunately. However, if you're using SQL 2000 then you could create a table-valued function rather than a view. These can take parameters just like stored procs but you can reference them directly from the FROM clause, just like a view. Quite neat!

If you're not on 2000 then you could put the select statement into the SP, to get the params, but insert the results into a temp table and then run the second query against that temp table rather than the view.

--James