return all using count(*) - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

return all using count(*)

Author

Topic

BillEdd
Starting Member

12 Posts

Posted - 2015-03-18 : 13:06:02

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

38200 Posts

Posted - 2015-03-18 : 13:10:03

Not enough info, but you could try LEFT JOIN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

BillEdd
Starting Member

12 Posts

Posted - 2015-03-18 : 14:24:00

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 14:27:27

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/

BillEdd
Starting Member

12 Posts

Posted - 2015-03-18 : 15:41:44

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 16:15:59

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/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 16:16:33

This too: http://stackoverflow.com/questions/27285478/sql-count-group-by-zero

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-19 : 02:47:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources