Jump to content

SQL count help


Recommended Posts

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

Link to post
Share on other sites

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
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...