SQL count help

[honkmaster 0]

Hi, I have an issue and hope someone can point me in the right direction. I have a table called production

Table production

Id	Job	Company	Status
—	——	———	———
123	001	Test 1	Active	
123	002	Test 1	Active
123	003	Test 1	Active	
123	004	Test 1	Complete
123	005	Test 1	Active	
123	006	Test 1	Complete
456	001	Test 2	Complete	
456	002	Test 2	Complete
456	003	Test 2	Active	
456	004	Test 2	Complete
456	005	Test 2	Active	
456	006	Test 2	Complete
789	001	Test 3	Complete	
789	002	Test 3	Active
789	003	Test 3	Active	
789	004	Test 3	Active
789	005	Test 3	Active	
789	006	Test 3	Active

I'm trying to run a query that returns the following

Company	Active	Complete
Test 1	4	2
Test 2	2	4
Test 3	5	1

So its DISTINCT Id and counting the different status and return as above

 

Any help would be great as I have been trying most of the day but without success

 

Cheers Chris

[Ingolme 1,035]

I don't think a single query can do that. With two queries you could do the following:

 

Count active:

SELECT Company, COUNT(*) FROM production WHERE Status = 'Active' GROUP BY Company

Count complete

SELECT Company, COUNT(*) FROM production WHERE Status = 'Complete' GROUP BY Company