Conditional Counting Return In SQL

Question

I've looked at the examples and I think I should be using a JOIN but I can't get the syntax right and it's nothing but errors. I need to return a table showing Counts based on criteria.

My procedure looks like this

SELECT  SalesMan, Count(SalesMan) AS SalesCount 
FROM  Customers
WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD' 
GROUP BY SalesMan 
ORDER BY SalesCount DESC  

SELECT DISTINCT SalesMan2, Count(Salesman2)   AS HalfCount  
FROM  Customers
WHERE SaleDate   Between @BeginDate And @EndDate AND Status = 'SOLD'   
GROUP BY SalesMan2   
ORDER BY HalfCount DESC

This Return two tables that look like this

SalesMan | SalesCount

BOB        8
ANDY       5
JOE        3

SalesMan2 | HalfCount

(blank)    40
ANDY       1
JACOB      1
ROB        1
JOE        1

The logic I'm trying to make work is.. If they're Salesman add 1, If they're the Salesman2 then add .5. Plus I get a bunch of empties I don't care about in the 2nd table. I don't need those.

What I need is for it to return this

Salesman | SalesCount

BOB       8
ANDY      5.5
JOE       3.5
JACOB     .5
ROB       .5

I've looked at a lot of answers but they use Joins with where clauses and strings. They also know what value they're supposed to match up to. Any help pointing me in the right direction would be greatly appreciated. Thanks in advance.

@JuanCarlosOropeza - wish I could upvote infinity your comment.... I've gotten caught in these traps too many times without asking that VERY important question. There I go coming up with an answer, then the OP says "Oh I use MySQL". >_< — Brien Foss
– Brien Foss, Commented Mar 9, 2018 at 18:27
Why do you need distinct on the second? Not clear. Not tagged. VTC — paparazzo
– paparazzo, Commented Mar 9, 2018 at 18:29
@Rich Which dbms???? It's one of the most important piece of information that you are missing!! — Eric
– Eric, Commented Mar 9, 2018 at 18:31

Gordon Linoff · Accepted Answer · 2018-03-09 18:45:52Z

1

One method uses union all:

SELECT SalesMan, SUM(cnt) AS SalesCount 
FROM  ((select c.SalesMan, 1 as cnt
        FROM Customers c
        WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD' 
       ) UNION ALL
      (SELECT SalesMan2, 0.5 as cnt
       FROM Customers c
       WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD'
      )
     ) c
GROUP BY SalesMan;

answered Mar 9, 2018 at 18:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

paparazzo Over a year ago

where SalesMan2 is not null

Rich Over a year ago

Thanks so much for your help. I'm going to dig down into the UNION and JOIN statements. I know enough sql to get into trouble that's about it. I notice you put 'FROM Customer c'. Why the c? I'm learning SQL from a book and it only goes so far.

Tom H · Accepted Answer · 2018-03-09 18:36:54Z

1

The database isn't normalized, which is going to make this query more difficult than it needs to be. Stuck with that structure though, here is where I would start:

SELECT
    COALESCE(NULLIF(SalesMan, ''), NULLIF(SalesMan2, '')),
    SUM(CASE WHEN COALESCE(SalesMan, '') <> '' THEN 1.0 ELSE 0.0 END) +
        SUM(CASE WHEN COALESCE(SalesMan2, '') <> '' THEN 0.5 ELSE 0.0 END) AS SalesCount
FROM Customers C1
FULL OUTER JOIN Customers C2 ON
    C2.SalesMan = C1.SalesMan2 AND
    C2.Status = 'SOLD' AND
    C2.SaleDate BETWEEN @BeginDate AND @EndDate
WHERE
    C1.Status = 'SOLD' AND
    C1.SaleDate BETWEEN @BeginDate AND @EndDate
GROUP BY
    COALESCE(NULLIF(SalesMan, ''), NULLIF(SalesMan2, ''))

This is untested and off the top of my head since you didn't include code to set up a test scenario. If it doesn't give the correct results then please let me know.

answered Mar 9, 2018 at 18:36

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

1 Comment

Gordon Linoff Over a year ago

I always find that filtering conditions with full outer join is rather counter-intuitive. Your query will return all sales people, even when those that match none of the conditions.

Rich · Accepted Answer · 2018-03-09 20:04:54Z

0

I really appreciate the help. I tried both solutions and Gordon's solution worked out of the box.

the procedure that worked is as follows

SELECT SalesMan, SUM(cnt) AS SalesCount 
FROM  ((select c.SalesMan, 1 as cnt
FROM Customers c
    WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD' 
   ) UNION ALL
  (SELECT SalesMan2, 0.5 as cnt
   FROM Customers c
   WHERE SaleDate Between @BeginDate And @EndDate AND Status = 'SOLD' AND 
   SalesMan2 <> ''
  )
 ) c
GROUP BY SalesMan ORDER BY SalesCount DESC ;

I added the AND SalesMan2 <> '' And Order By clauses and this works perfectly.

Also I'm using MSSQL. I'll remember to post that in the future.

answered Mar 9, 2018 at 20:04

Rich

131 bronze badge

Collectives™ on Stack Overflow

Conditional Counting Return In SQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related