I have this table
| ID | PhoneID | PhoneName | Active | Status |
|---|---|---|---|---|
| 1 | 1 | a | 1 | 1 |
| 2 | 1 | b | 0 | 2 |
| 3 | 2 | c | 1 | 1 |
| 4 | 2 | d | 1 | 1 |
| 5 | 2 | e | 0 | 3 |
| 6 | 3 | f | 1 | 1 |
| 7 | 3 | g | 1 | 1 |
| 8 | 3 | h | 1 | 1 |
| 9 | 4 | i | 0 | 4 |
| 10 | 4 | j | 1 | 1 |
And would like to make a query that gives this result as shown below. I want to get all rows where Status = 1. I also want to count the total amount of PhoneIDs and make it a column called Quantity in the query.
| ID | PhoneID | PhoneName | Active | Status | Quantity |
|---|---|---|---|---|---|
| 1 | 1 | a | 1 | 1 | 2 |
| 3 | 2 | c | 1 | 1 | 3 |
| 4 | 2 | d | 1 | 1 | 3 |
| 6 | 3 | f | 1 | 1 | 3 |
| 7 | 3 | g | 1 | 1 | 3 |
| 8 | 3 | h | 1 | 1 | 3 |
| 10 | 4 | j | 1 | 1 | 2 |
So far I have tried a query but it isn't displaying the right result.
SELECT ID, PhoneID, PhoneName, Active, Status, (SELECT Count(PhoneID) FROM Phones),
WHERE Status = 1
CodePudding user response：
SELECT Count(PhoneID) FROM Phones doesn't have a WHERE clause to limit it to the status you want.
CodePudding user response：
You need to correlate your subquery, try:
SELECT ID, PhoneID, PhoneName, Active, Status,
(SELECT Count(*) FROM Phones p2 WHERE p2.PhoneId = p.PhoneId) Quantity
FROM Phones p
WHERE Status = 1;
CodePudding user response：
You can do this with a subquery which returns the count of each PhoneID:
SELECT t.ID, t.PhoneID, t.PhoneName, t.Active, t.Status, q.[Quantity]
FROM Phones as t
inner join
(
select PhoneID, COUNT(PhoneID) as [Quantity]
from Phones
group by PhoneID
) as q
on q.[PhoneID] = t.[PhoneID]
WHERE t.Status = 1