[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • Last Modified:

Count Records within a group

I have data in a table in the following format:

      Title     Status           Date
     car1       working        1-1-04    
     car1       working        1-2-04
     car1       working        1-3-04
     car 2      working        1-3-04
     car 4      in progress   1-4-04
     car 5      not working  1-5-04

I would like to be able write an SQL statement that first groups all records first by their title, then returns the count of the status...I normally would build a query to group the data by car, then in a separate query count the records in the first query grouped by status.  In this case, I want to do it all in 1 statement.
So output would tell me total # status' for these cars:  Specifically,  Working= 2     In progress= 1     not working = 1


                                                 

0
BSDT
Asked:
BSDT
1 Solution
 
lilian-arnaudCommented:
try :

Select Status,count(distinct Title)
  From table
group by Status
0
 
peter57rCommented:
Hello BSDT,

SELECT Count([status='working') AS WKnt, Count([Status]='in progress') AS PKnt, Count([Status]='not working') AS NKnt FROM Mytablename;


Pete
0
 
vandalesmCommented:

select title, status, count(status) as status_count from tableName group by title, status

hope this helps
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tomgalloCommented:
Try this:

SELECT
 COUNT(Cars) as Total,
 Status
FROM
 Table
WHERE
  Status = 'working'
GROUP BY Status
UNION
SELECT
 COUNT(Cars) as Total,
 Status
FROM
 Table
WHERE
  Status = 'in progress'
GROUP BY Status
UNION
SELECT
 COUNT(Cars) as Total,
 Status
FROM
 Table
WHERE
  Status = 'not working'
GROUP BY Status

Hope that helps,
-tom
0
 
tomgalloCommented:
Sorry, you have to add DISTINCT (i forget):

SELECT DISTINCT COUNT(Title) as Total, Status FROM Table WHERE Status = 'working' GROUP BY Status
UNION
SELECT DISTINCT COUNT(Title) as Total, Status FROM Table WHERE Status = 'in progress' GROUP BY Status
UNION
SELECT DISTINCT COUNT(Cars) as Total, Status FROM Table WHERE Status = 'not working' GROUP BY Status
0
 
tomgalloCommented:
Hehe, I tried and again is not working... this will work:

SELECT COUNT(*) as Working FROM Table WHERE Status = 'working' GROUP BY Title
UNION
SELECT COUNT(*) as InProgress FROM Table WHERE Status = 'in progress' GROUP BY Title
UNION
SELECT COUNT(*) as NotWorking FROM Table WHERE Status = 'not working' GROUP BY Title

Sorry! This works!
-tom
0
 
HilaireCommented:
Since you just need the most recent status, you need to join a subquery as follows
Can you give it a try ?

select sum(case when a.status = 'working' then 1 else 0 end) as Working,
sum(case when a.status = 'in progress' then 1 else 0 end) as InProgress,
sum(case when a.status = 'not working' then 1 else 0 end) as NotWorking
from <YourTable> a
inner join (select title, max(date) as maxdate from <YourTable> group by title) b on a.Title = b.Title and a.date = b.maxdate

0
 
Wayne_OwenCommented:
You Could use a Crosstab Query as below,


TRANSFORM Count(Date) AS CountOfDate
SELECT Title
FROM Table1
GROUP BY Title
PIVOT Status;
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now