Compound select with 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 2008 Forums

Transact-SQL (2008)

Compound select with count

Author

Topic

mattschmidt
Starting Member

4 Posts

Posted - 2011-12-15 : 15:07:24

I've spent the majority of the day pounding on this one and am finally giving in. HELP PLEASE!
Here's what I'm looking for in table output:
blade.srv_id | frame.enc_id | SRVCOUNT
-------------+--------------+----------
1 | 101 | 5
2 | 101 | 5
3 | 101 | 5
4 | 101 | 5
5 | 101 | 5
11 | 102 | 3
12 | 102 | 3
13 | 102 | 3
20 | 104 | 6
30 | 104 | 6
40 | 104 | 6
50 | 104 | 6
60 | 104 | 6
70 | 104 | 6

My best guess-timate:

select blade.srv_id, frame.enc_id, select(count(blade.srv_id) as SRVCOUNT FROM blade INNER JOIN frame
ON blade.SRV_ENCL = frame.ENC_ID) AS Expr1
FROM blade INNER JOIN
frame ON blade.SRV_ENCL = frame.ENC_ID

I'm sure there's a "group by" in there somewhere in addition to the join but for the life of me I can't figure it out.

Thank you in advance for any feedback!~

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-15 : 15:12:32

I'm guessing



	SELECT blade.srv_id, frame.enc_id, COUNT(*) AS SRV_COUNT
      FROM blade 
INNER JOIN frame 
		ON blade.SRV_ENCL = frame.ENC_ID
  GROUP BY blade.srv_id, frame.enc_id

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mattschmidt
Starting Member

4 Posts

Posted - 2011-12-15 : 15:31:21

that's giving me a count of 1 for each. The only other thing I came up with was:

SELECT frame.enc_id, blade.srv_id, (SELECT COUNT(frame.ENC_ID) AS Expr1
FROM frame INNER JOIN blade ON frame.ENC_ID = blade.SRV_ENCL)
FROM blade
INNER JOIN frame
ON blade.SRV_ENCL = frame.ENC_ID
GROUP BY blade.srv_id, frame.enc_id

but that's giving me 1200 for each count.
It's almost like I need a union in there somewhere.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-15 : 15:48:13

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-15 : 15:51:25

There are probalby a bunch of ways to do this, but without knowing your schema or data here is quick and dirty guess:

select 
    blade.srv_id, 
    frame.enc_id, 
    T.crv_id_count
FROM 
    blade 
INNER JOIN
    frame 
    ON blade.SRV_ENCL = frame.ENC_ID
INNER JOIN
    (
        SELECT
            srv_id,
            COUNT(srv_id) AS crv_id_count
        FROM
            Blade
        GROUP BY
            srv_id
    ) AS T
    ON blade.srv_id = T.srv_id

mattschmidt
Starting Member

4 Posts

Posted - 2011-12-16 : 09:31:46

yes, I changed the names to protect the innocent and hopefully make what I'm trying to accomplish appear a little more intuitive.
Here's the DDL:

CREATE TABLE [dbo].[SRV](
[SRV_ID] [int] IDENTITY(1,1) NOT NULL,
[SRV_SN] [nvarchar](50) NULL,
[SRV_MODEL] [int] NULL,
[SRV_RAM] [int] NULL,
[SRV_CPU] [int] NULL,
[SRV_CORE] [int] NULL,
[SRV_TYPE] [int] NULL,
[SRV_ENCL] [int] NULL,
[SRV_USE] [int] NULL,
[SRV_COMMENTS] [nvarchar](50) NULL,
[SRV_PONUM] [nchar](10) NULL,
[SRV_BUOWNED] [bit] NULL,
CONSTRAINT [PK_SRV] PRIMARY KEY CLUSTERED
(
[SRV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ENCL](
[ENC_ID] [int] IDENTITY(1,1) NOT NULL,
[ENC_TYPE] [int] NULL,
[ENC_SN] [nvarchar](50) NULL,
[ENC_SITE] [nvarchar](50) NULL,
[ENC_FLOOR] [int] NULL,
[ENC_LOC] [nvarchar](50) NULL,
[ENC_NAME] [nvarchar](50) NULL,
[ENC_NW_type] [int] NULL,
[Enc_modules] [real] NULL,
[ENC_Parent] [int] NULL,
CONSTRAINT [PK_ENCL] PRIMARY KEY CLUSTERED
(
[ENC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 10:37:44

Since srv_Id is an IDENTITY, the count will always be 1

I thinkl you grouping needs to be something else




CREATE TABLE #ENCL (
[ENC_ID] [int] IDENTITY(1,1) NOT NULL,
[ENC_TYPE] [int] NULL,
CONSTRAINT [PK_ENCL] PRIMARY KEY CLUSTERED 

(
[ENC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE #SRV(
[SRV_ID] [int] IDENTITY(1,1) NOT NULL,
[SRV_ENCL] [int] NULL,
CONSTRAINT [PK_SRV] PRIMARY KEY CLUSTERED 
(
[SRV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO #ENCL([ENC_TYPE])
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 3 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4

INSERT INTO #SRV([SRV_ENCL])
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3 UNION ALL 
SELECT 3 UNION ALL 
SELECT 3 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4 UNION ALL 
SELECT 4
GO


	SELECT s.srv_id, e.enc_id, COUNT(*) AS SRV_COUNT
      FROM #SRV  s
INNER JOIN #ENCL e
		ON s.SRV_ENCL = e.ENC_ID
  GROUP BY s.srv_id, e.enc_id
GO


DROP TABLE #ENCL, #SRV
GO

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 10:38:54

maybe you want


	SELECT s.SRV_ENCL, e.enc_id, COUNT(*) AS SRV_COUNT
      FROM #SRV  s
INNER JOIN #ENCL e
		ON s.SRV_ENCL = e.ENC_ID
  GROUP BY s.SRV_ENCL, e.enc_id
GO

mattschmidt
Starting Member

4 Posts

Posted - 2011-12-16 : 11:23:06

That's it, thanks.
Need to rethink design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources