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]
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mikkilineniCommented:
SQL Server 2005's PIVOT operator allows you to rotate row level data, i think you can use that to get the desirable result.
0
gogetsomeAuthor Commented:
Sorry, I didn't know that it would be different between 2000 and 2005. This database is a 2000.
0
Jinesh KamdarCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Faiga DiegelSr Database EngineerCommented:
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.
0
Faiga DiegelSr Database EngineerCommented:
ohhh... i was actually typing mine before I got to read that thi was SQL 2000. CTE will not work on 2000.
0
gogetsomeAuthor Commented:
Thank you so much! A clear use of case.
0
Jinesh KamdarCommented:
Glad to be of help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.