We help IT Professionals succeed at work.

Select with multiple where conditions??

Hello, how do you accomplish a where condition for each column in a select statement?

Consider the statement below. How can Incorporate the comments to obtain the values for the columns????


select  count ([User]) as 'Number of Quotes',
      Sum (totalamount_q) as 'Total Quote Amount',

       count ([User]) as 'Number of Quotes Won', --where status =1
      Sum (totalamount_o) as 'Total Quote Won Amount', --where status =1
      
      count ([User]) as 'Number of Quotes Lost', --where status =2
      Sum (totalamount_q) as 'Total Quote Lost Amount', --where status =2

      count ([User]) as 'Number of Quotes Expired', --where status =3
      Sum (totalamount_q) as 'Total Quote Expired Amount', --where status =3
      [User]
from BB_SalesWinLossUsers
group by [User]
Comment
Watch Question

SQL Server 2005's PIVOT operator allows you to rotate row level data, i think you can use that to get the desirable result.

Author

Commented:
Sorry, I didn't know that it would be different between 2000 and 2005. This database is a 2000.
Try the CASE statement.
SELECT [User],
       COUNT(1)                                              AS 'Number of Quotes',
       SUM(totalamount_q)                                    AS 'Total Quote Amount', 
       SUM(CASE status WHEN 1 THEN 1 ELSE 0 END)             AS 'Number of Quotes Won',
       SUM(CASE status WHEN 1 THEN totalamount_o ELSE 0 END) AS 'Total Quote Won Amount',
       SUM(CASE status WHEN 2 THEN 1 ELSE 0 END)             AS 'Number of Quotes Lost',
       SUM(CASE status WHEN 2 THEN totalamount_o ELSE 0 END) AS 'Total Quote Lost Amount',
       SUM(CASE status WHEN 3 THEN 1 ELSE 0 END)             AS 'Number of Quotes Expired',
       SUM(CASE status WHEN 3 THEN totalamount_o ELSE 0 END) AS 'Total Quote Expired Amount'
FROM BB_SalesWinLossUsers 
GROUP BY [User]

Open in new window

Faiga DiegelSr Database Engineer
CERTIFIED EXPERT

Commented:
Use CTE (common table expression)

WITH TotalAmount (User, NumberofQuotes) AS
(
      SELECT Distinct User, count ([User]) as 'Number of Quotes',
         Sum (totalamount_q) as 'Total Quote Amount',
     FROM BB_SalesWinLossUsers
     GROUP BY USER
),

QUOTESWON (user, NumberofQuotesWon, TotalAmount0) AS
(
       SELECT DISTINCT User, count ([User]) as 'Number of Quotes Won' ,
                  Sum (totalamount_o) as 'Total Quote Won Amount'
      FROM BB_SalesWinLossUsers
      WHERE status =1
     GROUP BY USER
 )

QUOTESLOST  (User, NumberOfQuotesLost, LostAmount)  AS  
(
      SELECT DISTICT User count ([User]) as 'Number of Quotes Lost',      
                Sum (totalamount_q) as 'Total Quote Lost Amount'
     FROM BB_SalesWinLossUsers
     WHERE status =2
    GROUP BY USER
)

QUOTESEXPIRED (User, NumberOfQuotesExpired, ExpiredAmount) AS
(
      SELECT DISTINCT User, count ([User]) as 'Number of Quotes Expired'
           Sum (totalamount_q) as 'Total Quote Expired Amount'    
      FROM BB_SalesWinLossUsers
     WHER status =3
    GROUP BY USER
)

-- then combine all
SELECT TA.User, NumberofQuotes, NumberOfQuotesLost, LostAmount, NumberOfQuotesLost, LostAmount, NumberOfQuotesExpired, ExpiredAmount
FROM TotalAmount TA
  INNER JOIN QUOTESWON QW ON QW.User = TA.User
  INNER JOIN QUOTESLOST QL ON QL.User = TA.User
  INNER JOIN QUOTESEXPIRED QE ON QE.User = TA.User

syntax may have error since I cannot replicate it here on my machine. The idea is to create CTEs for each set of your condition and then join it by User column.
Faiga DiegelSr Database Engineer
CERTIFIED EXPERT

Commented:
ohhh... i was actually typing mine before I got to read that thi was SQL 2000. CTE will not work on 2000.

Author

Commented:
Thank you so much! A clear use of case.
Glad to be of help :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.