1

Given the following table:

Chain    Name
123      Company 1
124      Other Company 1
123      Whatever Company
125      This One
126      That One
125      Another One
127      Last One

I get the following results when I do a Count on the Chain column:

123      2
124      1
125      2
126      1
127      1

Is it possible to group Chain 123 and 124 so they're counted together? Also group 125 and 126? The modified results would look like this:

123/124  3
125/126  3
127      1

My SQL looks like this:

SELECT Table1.Chain, Count(*) as [Count]
FROM Table1 LEFT JOIN Table2 on Table1.Chain = Table2.Chain
WHERE (((Table1.Chain) IN (Table2.Chain)))
GROUP BY Table1.Chain
ORDER BY Table1.Chain;

Thank you!

Jeff Brady
  • 1,454
  • 7
  • 35
  • 56

5 Answers5

3

Depending upon your needs, this might be a bit of a hack, but I would probably add a table to store the Chain and ChainGroup that you are seeking. Something like this:

Chain  ChainGroup
123      123/124
124      123/124
125      125/126
126      125/126
127      127/128

Then, in the query, I would join to this table and instead of grouping by Chain I would group by ChainGroup.

I would prefer this over something like a nested IIF statement as those get pretty difficult to debug, and odds are you'll have additional groupings in the future which would be trivial to add to the table and have the new grouping automatically appear in the query.

Tim Lentine
  • 7,782
  • 5
  • 35
  • 40
2

yes, you can:

SELECT min(Table1.Chain) & '/' & max(Table1.Chain) as chain, Count(*) as [Count]
FROM Table1 LEFT JOIN Table2 on Table1.Chain = Table2.Chain
WHERE (((Table1.Chain) IN (Table2.Chain)))
GROUP BY int((Table1.Chain-1)/2)
ORDER BY min(Table1.Chain);
Saic Siquot
  • 6,513
  • 5
  • 34
  • 56
1

You can use a nested Iif statement. Hopefully I've got all my parentheses right below! :-)

SELECT Iif(Table1.Chain="123", "123/124",
         Iif(Table1.Chain="124", "123/124",
           Iif(Table1.Chain="125", "125/126",
             Iif(Table1.Chain="126", "125/126", Table1.Chain)))) as [Chain]
 , Count(*) as [Count]
FROM Table1 LEFT JOIN Table2 on Table1.Chain = Table2.Chain
WHERE (((Table1.Chain) IN (Table2.Chain)))
GROUP BY Iif(Table1.Chain="123", "123/124",
           Iif(Table1.Chain="124", "123/124",
             Iif(Table1.Chain="125", "125/126",
               Iif(Table1.Chain="126", "125/126", Table1.Chain))))
ORDER BY Table1.Chain;

You could also move the case statement into a subquery in your from clause or a common table expression if you don't want to write it twice in your query.

Jeff Rosenberg
  • 3,522
  • 1
  • 18
  • 38
  • I never knew you could use `case` like this! I will give it a shot .. thank you! – Jeff Brady Apr 11 '13 at 15:42
  • 2
    Good idea, but I don't believe MS Access supports the CASE statement in queries... Might need to instead use a nested IIF statement instead. – Tim Lentine Apr 11 '13 at 15:43
  • 1
    @JeffBrady since you're using Access, you can't do it quite like this. You'll need to use Iif instead. I'll edit my answer. – Jeff Rosenberg Apr 11 '13 at 15:44
  • Nested `IIf` expressions become challenging. Consider a `Switch` expression instead: http://stackoverflow.com/a/15776608/77335 – HansUp Apr 11 '13 at 15:54
  • @JeffRosenberg, this gives me a `You tried to execute a query that does not include the specified expression 'Table1.Chain' as part of an aggregate function` – Jeff Brady Apr 11 '13 at 16:00
  • 1
    It's possible that in Access SQL, you could simply say `GROUP BY [Chain]` and `ORDER BY [Chain]`. Honestly, though, I'd recommend Tim's solution. – Jeff Rosenberg Apr 11 '13 at 16:04
  • Actually, I just removed the `ORDER BY` and it worked! Thank you! I will also check Tim's solution but this does what I needed :) – Jeff Brady Apr 11 '13 at 16:23
1

consider something like:

SELECT
chain_group, COUNT(*) FROM (
SELECT 
Table1.Chain, 
switch(Table1.Chain IN("123","124"), "123/124",
Table1.Chain IN("125","126"),"125/126",
Table1.Chain) AS chain_group
FROM 
Table1 INNER JOIN 
Table2 ON 
Table1.Chain = Table2.Chain) t
GROUP BY chain_group
ORDER BY chain_group
Beth
  • 9,531
  • 1
  • 24
  • 43
  • this gives an error `Wrong number of arguements used with function in query expression 'Iif(Table1.Chain IN("123","124"), "123/124", Table1.Chain IN("125","126"),"125/126", Table1.Chain` – Jeff Brady Apr 11 '13 at 16:16
  • 1
    sorry, should have used the 'switch' function instead of IIF – Beth Apr 11 '13 at 16:20
0

You can see the below example ----- Make main Table

CREATE TABLE #test 
( id int , Name varchar(100))

INSERT #test(id,Name)
values (123,'Company 1'),
(124,'Other Company 1'),
(123,      'Whatever Company'),
(125,      'This One'),
(126 ,     'That One'),
(125,      'Another One'),
(127,      'Last One')

CREATE TABLE #temp
(rowID INT IDENTITY(1,1) , ID INT ,cnt INT )

CREATE TABLE #tempResult
(ID VARCHAR(20) ,cnt INT )

INSERT INTO #temp(ID,cnt)
SELECT  ID ,COUNT(1) cnt FROM  #test GROUP BY ID 

DECLARE @rowCnt INT , @TotalCnt INT , @even INT , @odd INT , 
@idNum VARCHAR(20) , @valueCnt INT , @inStart INT = 1

SET @rowCnt  = 1
SET @even = 1 
SET @odd = 2

SELECT @TotalCnt = COUNT(1) FROM #temp

WHILE @rowCnt <= @TotalCnt
BEGIN
    SET @inStart = 1
    SET @odd = @rowCnt 
    SET @even = @rowCnt + 1
    SET @idNum = ''
    SET @valueCnt = 0

    WHILE @inStart <= 2
    BEGIN

    IF @inStart = 1
    Begin
     SELECT @idNum = Convert(VARCHAR(5),ID) , @valueCnt = cnt 
     FROM #temp WHERE rowID = @odd
    End 
    ELSE
    BEGIN
    SELECT @idNum = @idNum + '/' + Convert(VARCHAR(5),ID) , @valueCnt = @valueCnt  + cnt 
FROM #temp WHERE rowID = @even  
    END
        SET @inStart = @inStart + 1
    END
    INSERT INTO #tempResult (ID, Cnt) 
    VALUES (@idNum,@valueCnt)

    SET @rowCnt = @rowCnt + 2
END

SELECT *
FROM #tempResult
user1608817
  • 471
  • 3
  • 4