Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A simple query needs your help Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 10:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 806, Visits: 1,440
I have two tables to be joined
Doc:
ID, DivID

Division:
ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL
)

CREATE TABLE [dbo].[Division](
[ID] [int] NULL,
[Div] [int] NULL
)
insert into Doc (ID, DivID) Values (1, 1)
insert into Doc (ID, DivID) Values (2, 2)
insert into Doc (ID, DivID) Values (3, 1)
insert into Doc (ID, DivID) Values (4, 3)
insert into Doc (ID, DivID) Values (5, 2)
insert into Doc (ID, DivID) Values (6, 8)
insert into Doc (ID, DivID) Values (7, 7)

insert into Division (ID, Div) Values (1, 'Div1')
insert into Division (ID, Div) Values (2, 'Div2')
insert into Division (ID, Div) Values (3, 'Div3')
insert into Division (ID, Div) Values (4, 'Div4')
insert into Division (ID, Div) Values (5, 'Div5')
insert into Division (ID, Div) Values (6, 'Div6')
insert into Division (ID, Div) Values (7, 'Div7')
insert into Division (ID, Div) Values (8, 'Div8')


As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID


Thank you very much for your help
Post #1554562
Posted Tuesday, March 25, 2014 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
Can you provide a sample output of what you would expect given your data? I am struggling to understand what data you want returned.

MCITP SQL 2005, MCSA SQL 2012
Post #1554563
Posted Tuesday, March 25, 2014 10:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 806, Visits: 1,440
RTaylor2208 (3/25/2014)
Can you provide a sample output of what you would expect given your data? I am struggling to understand what data you want returned.


I am looking for an output like:
DivID Total
1 1
2 2
3 0
4 1
5 2
6 0
7 1
8 0

Thanks.
Post #1554580
Posted Tuesday, March 25, 2014 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 23,210, Visits: 31,892
Is this a good start? Without your expected results based on the sample data it is hard to know what you are actually looking for here.


CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL
)

CREATE TABLE [dbo].[Division](
[ID] [int] NULL,
[Div] varchar(16) NULL
)
insert into Doc (ID, DivID) Values (1, 1)
insert into Doc (ID, DivID) Values (2, 2)
insert into Doc (ID, DivID) Values (3, 1)
insert into Doc (ID, DivID) Values (4, 3)
insert into Doc (ID, DivID) Values (5, 2)
insert into Doc (ID, DivID) Values (6, 8)
insert into Doc (ID, DivID) Values (7, 7)

insert into Division (ID, Div) Values (1, 'Div1')
insert into Division (ID, Div) Values (2, 'Div2')
insert into Division (ID, Div) Values (3, 'Div3')
insert into Division (ID, Div) Values (4, 'Div4')
insert into Division (ID, Div) Values (5, 'Div5')
insert into Division (ID, Div) Values (6, 'Div6')
insert into Division (ID, Div) Values (7, 'Div7')
insert into Division (ID, Div) Values (8, 'Div8')

select
*
from
dbo.Division d1
left outer join dbo.Doc d2
on (d1.ID = d2.DivID)
go

drop table dbo.Doc;
drop table dbo.Division;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1554581
Posted Tuesday, March 25, 2014 10:42 AM This worked for the OP Answer marked as solution
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:46 AM
Points: 61, Visits: 522
Hi,

My Take on it.. :)

WITH DocCTE (DivIDCte, DivIDCount)
AS
(SELECT DivID,
COUNT(DivID)
FROM Doc GROUP BY DivID)

SELECT ID,
Div,
'Count' = ISNULL(DivIDCount,0)
FROM Division d
LEFT OUTER JOIN DocCTE dc ON dc.DivIDCte = d.ID


============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1554588
Posted Tuesday, March 25, 2014 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 806, Visits: 1,440
dbalmf (3/25/2014)
Hi,

My Take on it.. :)

WITH DocCTE (DivIDCte, DivIDCount)
AS
(SELECT DivID,
COUNT(DivID)
FROM Doc GROUP BY DivID)

SELECT ID,
Div,
'Count' = ISNULL(DivIDCount,0)
FROM Division d
LEFT OUTER JOIN DocCTE dc ON dc.DivIDCte = d.ID


Thank you and Lynn very much, this is what I need
Post #1554594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse