CODEWITHSUNDEEP
sql
user3371452
user3371452

Reputation: 23

Select statement to get 2 value from column in a table

Hi i want to select 2 value from a column in the same table, in the auction table there is a column call status, the status can be either Paid or Unsold, i want to get these two value as 2 column for example i want to get the the number of d that has status paid and the number of id that has status unsold

SELECT Auction.AuctionID AS Auction, 
       DATENAME(mm, Auction.EndDate) AS Month 
  FROM Auction 
 WHERE (Auction.Status = 'Unsold')



SELECT Auction.AuctionID AS Auction, 
       DATENAME(mm, Auction.EndDate) AS Month 
  FROM Auction 
 WHERE (Auction.Status = 'Paid')

Upvotes: 0

Views: 371

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25351

From your comments to your other question, it seems you need two sub-queries like this:

SELECT unsold.total, paid.total 
FROM (
  SELECT COUNT(Auction.AuctionID) AS total
  FROM Auction 
  INNER JOIN Item ON Auction.ItemID = Item.ItemID 
  WHERE (Auction.Status = 'UnSold') AND (Item.SellerID = 201)
  GROUP BY Auction.Status
) AS unsold,
(
  SELECT COUNT(BID.BidID) AS total
  FROM BID
  WHERE (BID.Status = 'Paid')
  GROUP BY BID.Status
) AS paid

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Use a case

SELECT case when Status = 'Unsold' then AuctionID end AS Auction_Unsold, 
       case when Status = 'Paid' then AuctionID end AS Auction_Paid, 
FROM Auction 
WHERE Status IN ('Unsold','Paid')

Upvotes: 1

Related Questions