December 17, 2003 at 11:53 pm
I have 3 columns in my sql table which are orderNo,CustId,Month
I want to get a sql query which will return records with 3 columns.
The first column will have the count of orderNo for a particular selected customer for a month,
the second column will have the count of orderNo for all customers for that month,
the third column will be that particular month
So in other words I want the count of orders for a customer for a month and the second column the count
of all orders for that month and third the month itself.
How do I write this query. I am not able to assemble it after a lot of try.
Thank you.
December 18, 2003 at 2:48 am
You could do something along the lines of:
Declare @CustMonthOrders numeric
Declare @TotalMonthOrders numeric
Declare @Month numeric
Set @Month = 1
Set @CustId = 20
Set @CustMonthOrders = (Select Count(orderNo) From Orders
Where Month = @Month and CustId = @CustId)
Set @TotalMonthOrders = (Select Count(orderNo) From Orders
Where Month = @Month)
Select @CustMonthOrders as CustomerOrders,
@TotalMonthOrders as TotalOrders,
@Month as Month
Obviously you will need to replace the @Month and @CustId to suit your own data....
December 18, 2003 at 3:09 am
Thanks for replying
but the @Month is not passed to the query, only the @CustId is passed.
So in that case I tried giving group by month in
Set @CustMonthOrders = (Select Count(orderNo) From Orders
Where Month = @Month and CustId = @CustId group by month)
Set @TotalMonthOrders = (Select Count(orderNo) From Orders
Where Month = @Month group by month)
but it is showing the error 'Subquery returned more than 1 value'
December 18, 2003 at 3:27 am
In that case try:
Select Count(orderNo) as COrders, Month as theMonth into #TempCustOrders
From Orders
Where CustId = @CustId
Group by Month
Select t.COrders as CustOrders, Count(OrderNo) as TotalMonthOrders, Month
From Orders
inner Join #TempCustOrders t on t.theMonth = Month
Group by t.COrders, Month
Drop Table #TempCustOrders
December 18, 2003 at 4:31 am
I tried writing the 2nd select statememt 1st and then the 1st select statement but i get an error like 'ambiguous column name 'theMonth'.
I did this because for a particular Customer @CustId(which is passed) there could possibly not be a record(order) present for some month, where as other customers can have orders for that month.
December 18, 2003 at 4:50 am
aaahhh then I belive this should give the required results:
Select Count(orderNo) as TMOrders, [Month] as theMonth into #TempTMonthOrders
From Orders
Group by [Month]
Select Count(OrderNo) as CustOrders, [Month] as theMonth into #TempCOrders
From Orders
Where CustId = @CustId
Group by [Month]
Select isNull(tco.CustOrders,0) as CustomerOrders, tmo.TMOrders as TotalMonthOrders, tmo.theMonth as [Month]
from #TempTMonthOrders tmo
left Outer Join #TempCOrders tco on tco.theMonth = tmo.theMonth
Drop Table #TempTMonthOrders
Drop Table #TempCOrders
December 18, 2003 at 5:36 am
use northwind
go
DECLARE @custid nchar(5)
SET @custid = N'SAVEA'
SELECT SUM(CASE CustomerId WHEN @custid THEN 1 ELSE 0 END) OrdersByCust,
COUNT(*) TotalOrders,
CONVERT(char(7), OrderDate,120) YrMonth
FROM Orders
GROUP BY CONVERT(char(7),OrderDate,120)
ORDER BY YrMonth
If you have months with no orders from any customers, you would need to use some form of numbers table.
--Jonathan
--Jonathan
December 18, 2003 at 6:49 am
thanks both worked..probably using temp tables was the idea.
There is one more thing
In my other form's query I have
select (col1/col2) as Amt from Trades group by Month
now if col2 has a value 0(zero) i get an divide by zero encountered error
December 18, 2003 at 7:54 am
use a select Case statement i.e.
Select Result = Case col2
when 0 then 0
else col1/col2
end
From table
December 18, 2003 at 8:13 am
quote:
thanks both worked..probably using temp tables was the idea.There is one more thing
In my other form's query I have
select (col1/col2) as Amt from Trades group by Month
now if col2 has a value 0(zero) i get an divide by zero encountered error
SELECT Col1/NULLIF(Col2,0)
--Jonathan
--Jonathan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply