mysql query left join + count

Question

I want a set of data from Order files that give me CustomerNumber, Order Number, Product, Quantity, Price in addition to number of rows in order detail file for each order. I am having trouble with the last part.

Select Header.CustNo, Header.OrderNumber, Detail.SKU, 
   Detail.Quantity, Detail.Price, Count(Detail2) from Header
   left join Detail on (Header.OrderNumber= Detail.OrderNumber )
   left join Detail as Detail2 on (Header.OrderNumber= Detail2.OrderNumber )

there is a missing comma here : Detail.Price Count(Detail2) Also I don't get why you do twice the same join. — Jeremy D
– Jeremy D, Commented Jan 12, 2012 at 1:59
second time I want to do a count on detail lines, but this is clearly not the syntax. I am reading up on subselects but my head she is hurting — Mustapha George
– Mustapha George, Commented Jan 12, 2012 at 2:01
You're missing a group-by clause. You're got multiple fields that are NOT being aggregated, and mysql will not know how to arrange things to do the count(). — Marc B
– Marc B, Commented Jan 12, 2012 at 2:02
I want to group on order number.. in a subselect or WITH clause? — Mustapha George
– Mustapha George, Commented Jan 12, 2012 at 2:03

ruakh · Accepted Answer · 2012-01-12 02:07:57Z

2

Is this what you mean? :

SELECT Header.CustNo,
       Header.OrderNumber,
       Detail.SKU, 
       Detail.Quantity,
       Detail.Price,
       ( SELECT COUNT(1)
           FROM Detail AS Detail2
          WHERE Detail2.OrderNumber = Header.OrderNumber
       )
  FROM Header
  LEFT
  JOIN Detail
    ON Header.OrderNumber = Detail.OrderNumber
;

answered Jan 12, 2012 at 2:07

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mustapha George Over a year ago

dont't need a GROUP BY in subselect?

ruakh Over a year ago

@MustaphaGeorge: No, because you only want it to return a single row. (Notice that it's a correlated subquery: it refers to the Header.OrderNumber from the outer query.)

Mustapha George Over a year ago

bonzai! it works!! much simpler than what I was working on with temporary tables..

Collectives™ on Stack Overflow

mysql query left join + count

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related