Grouping and Functions??? 1

[Keyth]

Hi all.

I would like to group the following categories so that there is only one line per category and a value in both columns to indicate ordered qtys and not on order qtys

Results:

Handwork 5 NULL
Handwork NULL 30
Merchant NULL 11
POR NULL 583
POR 75 NULL
Reelsale NULL 255
Reelsale 14 NULL
Singles NULL 829
Singles 115 NULL
Singles Or POR NULL 28
Singles Or POR 2 NULL

Current Query Text:
SELECT
ManufactureMethod = qi.MANUFACTURE_METHOD,
OrderedYes = (Select Count(qi.Ordered) Where qi.ordered = 1),
OrderedNo = (Select Count(qi.Ordered) Where qi.ordered = 0)
FROM Quote_Header as qh, Quote_Item as qi
Where qh.Quote_Date between '20070301' and '20070331' and qi.quote_id = qh.quote_id and qi.is_memo_item = 0 and qi.PRICE_BREAK = 0
GROUP BY qi.MANUFACTURE_METHOD, qi.ordered
ORDER BY MANUFACTURE_METHOD

I may have gone about this completely the wrong way but could somebody point out how to do this?

Thanks,
Keyth

 

[Otto]

What about this (supposed qi.ordered is bit):
select
ManufactureMethod = qi.MANUFACTURE_METHOD,
OrderedYes = sum(cast(qi.ordered as int)),
OrderedNo = sum(1-cast(qi.ordered as int))
FROM Quote_Header as qh, Quote_Item as qi
Where qh.Quote_Date between '20070301' and '20070331' and qi.quote_id = qh.quote_id and qi.is_memo_item = 0 and qi.PRICE_BREAK = 0
GROUP BY qi.MANUFACTURE_METHOD
ORDER BY MANUFACTURE_METHOD

 

[Keyth]

Excellent!! That worked a treat thanks Otto.

 

[SimonSellick]

Keyth,

Your GROUP BY should contain only the columns for which you want to see all of the values, in this case, MANUFACTURE_METHOD. All of the other values should be group functions, so for your total counts, use sum() and assign 1 or 0 depending on whether you are counting ordered or unordered parts:

select
  qi.Manufacture_Method,
  sum(case qi.Ordered when 1 then 1 else 0 end) OrderedYes,
  sum(case qi.Ordered when 1 then 0 else 1 end) OrderedNo
from
  Quote_Header as qh, Quote_Item as qi 
where 
  qh.Quote_Date between '20070301' and '20070331' and
  qi.quote_id = qh.quote_id and
  qi.is_memo_item = 0 and
  qi.Price_Break = 0 
GROUP BY
  qi.Manufacture_Method
ORDER BY 
  Manufacture_Method

Hope this helps.

 

[SimonSellick]

Oops, too slow.