CODEWITHSUNDEEP
sqlsql-server-2008aggregate-functions
franglais
franglais

Reputation: 938

How to select a value based on a count(*) in a subquery

I'm running a query below which won't work as I can't put an aggregate in a where clause, but given that it's in a subquery I'm not entirely sure what the best way forward is from here...!

The line that doesn't work is:

        (select gradeid from commissionconsultants where UserId = ic.primaryconsultantid) 
        and starters = COUNT(*) ) from InvoiceCommissions ic

And the full query is as follows:

select u.username + ' ' + u.surname as UserName, 
ic.primaryconsultantid, 
COUNT(ic.invoiceid) as starters, 
DATENAME(mm,ic.invoiceissueddate) AS [month], 
DATEPART(yy,ic.invoiceissueddate) as [year], 
cast(SUM((ic.value / ic.exchangerate) * (ic.primaryconsultantperc / 100)) as numeric(8,2)) AS totalvalue, 
(select threshold from commissiongrades where gradeid = 
    (select gradeid from commissionconsultants where UserId = ic.primaryconsultantid) ) AS Threshold, 
(select percentage from commissiongradevalues where gradeid = 
    (select gradeid from commissionconsultants where UserId = ic.primaryconsultantid) 
        and starters = COUNT(*) ) from InvoiceCommissions ic
inner join commissionconsultants cc on cc.userid = ic.primaryconsultantid
inner join Users u on u.UserId = ic.primaryconsultantid
group by primaryconsultantid, DATENAME(mm,invoiceissueddate), DATEPART(yy,invoiceissueddate), u.username + ' ' + u.surname

What, in essence, I am trying to do is lookup the commission percentage from a table (commissiongradevalues) based on the grade of the consultant (found in commissionconsultants) and the number of placements made in a given month.

e.g. if a consultant has made one placement in a month, they will have XX commission percentage, and if they have made two placements in a month they will have YY commission percentage.

Any ideas?

Upvotes: 0

Views: 155

Answers (3)

Robert Co
Robert Co

Reputation: 1715

Here is my best guess on what you are trying to do.

WITH ic AS
(
SELECT ic.primaryconsultantid
     , COUNT(ic.invoiceid) as starters
     , DATENAME(mm,ic.invoiceissueddate) AS [month]
     , DATEPART(yy,ic.invoiceissueddate) as [year]
     , CAST(SUM((ic.value / ic.exchangerate) * (ic.primaryconsultantperc / 100)) as numeric(8,2)) AS totalvalue
  FROM InvoiceCommissions ic
 GROUP BY primaryconsultantid
     , DATENAME(mm,invoiceissueddate)
     , DATEPART(yy,invoiceissueddate)
)    
SELECT u.username + ' ' + u.surname AS UserName
     , ic.primaryconsultantid
     , ic.starters
     , ic.month
     , ic.year
     , ic.totalvalue
     , cg.threshold 
     , cgv.percentage 
  FROM ic
 INNER JOIN Users u 
    ON u.UserId = ic.primaryconsultantid
 INNER JOIN commissionconsultants cc
    ON cc.UserId = ic.primaryconsultantid
  LEFT OUTER commissiongrades cg
    ON cg.gradeid = cc.gradeid
  LEFT OUTER commissiongradevalues cgv
    ON cgv.gradeid = cc.gradeid
   AND cgv.starters = ic.starters

However, there are wierness in your query that I don't get.

  1. Why did you put your threshhold and percentage in SELECT? Are you expected a missing grade id? If not, change the LEFT OUTER to INNER.
  2. Why do you need to get commission if you already have ic.primaryconsultantperc? Is this a contingent commission? If it is, how come it's not added to the totalvalue?

Anyhow, enjoy!

Upvotes: 1

roman
roman

Reputation: 117530

I suggest to use just one main subquery when you do grouping and then do all joins. It's hard to write query without test data.
Anyway as far as I see - you'r using two subqueries on commissionconsultants and actually you need only one join on this table.

select
    ic.UserName, 
    ic.primaryconsultantid, 
    ic.starters, 
    ic.[month],
    ic.[year],
    ic.totalvalue,
    cg.threshold as Threshold, 
    cgv.percentage as Percentage
from
(
    select
        u.username + ' ' + u.surname as UserName, 
        tt.primaryconsultantid, 
        count(tt.invoiceid) as starters,
        datename(mm, tt.invoiceissueddate) as [month], 
        datepartT(yy, tt.invoiceissueddate) as [year],
        cast(sum((tt.value / tt.exchangerate) * (tt.primaryconsultantperc / 100)) as numeric(8,2)) as totalvalue,
    from InvoiceCommissions as tt
        inner join Users as u on u.userid = tt.primaryconsultantid
    group by
        tt.primaryconsultantid, 
        datename(mm, tt.invoiceissueddate), datepart(yy, tt.invoiceissueddate),
        u.username + ' ' + u.surname
) as ic
    inner join commissionconsultants as cc on cc.userid = ic.primaryconsultantid
    inner join commissiongrades as cg on cg.gradeid = cc.gradeid
    inner join commissiongradevalues as cgv on cgv.gradeid = cc.gradeid and cgv.starters = ic.starters

Upvotes: 0

Andomar
Andomar

Reputation: 238246

Replace COUNT(*) with:

(select count(*) from commissionconsultants)

Upvotes: 0

Related Questions