Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 return all using count(*)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BillEdd
Starting Member

12 Posts

Posted - 03/18/2015 :  13:06:02  Show Profile  Reply with Quote
I need to return a record for each type in my IN clause and not just the records that have a count of > 0. Not sure how to do this.

Desired sample results

MFN 0
PRM 10
BCO 0

Select o.ORD_TYPE, count(*) as NumberOfOrders
From Orders o
join Emps r on r.EmpNo = o.ORD_Emp_NO
join Order_Type_Groups g on g.Ord_TYPE = o.ORD_Type
Where year(ORD_DATE) = '2014'
and ORD_TYPE in ('MFN', 'BCO', 'PRM')
and left(o.ORD_Acct_Num ,3)='NPC'
group by ORD_TYPE



Thanks for your help

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/18/2015 :  13:10:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Not enough info, but you could try LEFT JOIN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

BillEdd
Starting Member

12 Posts

Posted - 03/18/2015 :  14:24:00  Show Profile  Reply with Quote
Tara:

Thanks for your reply. What more info do you need? And if possible based on the info I provided, could you please provide an example using your suggestion?

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/18/2015 :  14:27:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
We'd need to see sample data from the tables involved that illustrate the issue.

left join Order_Type_Groups g on g.Ord_TYPE = o.ORD_Type

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

BillEdd
Starting Member

12 Posts

Posted - 03/18/2015 :  15:41:44  Show Profile  Reply with Quote
Tara:
Thanks for your reply. Not sure I can provide that without altering it a lot for data confidentiality reasons. I tried your suggested join but got the same results as I did without it.

To try to illustrate, the Orders table has different types of orders whose o.ord_type is listed in the Order_Type_Groups table. The Emps table join is used to just get orders for a certain type of salesperson. That should have read:
join Emps on r.EmpNo = o.ORD_Emp_No and r.EmpStatus = 'A'

Not sure if this helps or not. In my example there were no orders for ORD_Type of MFN or ORD_Type of BCO. There were 10 orders for Ord_Type of PRM.

If this is still not enough info then I will close the post and say thanks for your help working with what you had to work with.

Thanks
Bill
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/18/2015 :  16:15:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
I think what you want to do is described here: http://stackoverflow.com/questions/1917516/sql-count-with-group-by-not-returning-0-zero-records

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/18/2015 :  16:16:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
This too: http://stackoverflow.com/questions/27285478/sql-count-group-by-zero

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/19/2015 :  02:47:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you try this?

Select o.ORD_TYPE, count(*) as NumberOfOrders
From Orders o
join Emps r on r.EmpNo = o.ORD_Emp_NO
left join Order_Type_Groups g on g.Ord_TYPE = o.ORD_Type
and ORD_TYPE in ('MFN', 'BCO', 'PRM')
Where year(ORD_DATE) = '2014'
and left(o.ORD_Acct_Num ,3)='NPC'
group by ORD_TYPE


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000