CODEWITHSUNDEEP
sql-server
Christopher
Christopher

Reputation: 13

How do I use SQL to count two separate rows, then display the separate counts

I've checked through here on how to use group by to count rows, but I think I am implementing it wrong, or missing something.

What I have: Two columns with data. Machine_GroupID ( I extract the client name from this). The second column, fieldValue, contains the ship date of the Machine(I extract the year only from this).

SQL 2012 server

Example: 

+------------------------------+-----------------------+
| Machine_GroupID(Client Name) | fieldValue(Ship Date) |
+------------------------------+-----------------------+
| Site1.clientA                | 2015-05-07            |
| Site2.clientA                | 2014-01-06            |
| Department.Site1.clientA     | 2015-02-05            |
| Site1.clientB                | 2014-03-04            |
| Department.Site1.ClientC     | 2015-10-01            |
+------------------------------+-----------------------+

What I am trying to do:

I am trying to generate a report to show all of the workstations a client purchased in a certain year. This will end up being a report in reportviewer, or something useful to display the data to our Executive team.

Desired Report example:

Machines purchased in 2015
+---------+------------------------+
| ClientA | 2(Count of fieldValue) |
+---------+------------------------+
| ClientC |                      1 |
+---------+------------------------+

Machines purchased in 2014
+---------+---+
| ClientA | 1 |
+---------+---+
| ClientB | 1 |
+---------+---+

My Code so far:

select count(*), reverse(left(reverse(Machine_GroupID) ,
charindex('.',reverse(Machine_GroupID))-1)) as Client , 
LEFT(fieldValue,4) AS "Ship Year"
from dbo.vSystemInfoManual 
where fieldName = 'Ship Date' 
group by fieldValue, Machine_GroupID

This code generates a table that looks like the following:

+---+----------+-----------+
|   |  Client  | Ship Year |
+---+----------+-----------+
| 1 | ClientA  |      2015 |
| 1 | ClientA  |      2015 |
| 1 | ClientA  |      2014 |
| 1 | ClientB  |      2014 |
| 1 | ClientC  |      2015 |
+---+----------+-----------+

Is there a change that I can make to my code to make this possible ? Am I trying to do too much with this query ? I am still learning SQL so any help is definitely appreciated! Thank you.

Upvotes: 1

Views: 47

Answers (1)

Lamak
Lamak

Reputation: 70648

Your sample data doesn't really go along with the results you posted. Anyway, I would change the way you are getting the Client value by using the PARSENAME function:

SELECT PARSENAME(Machine_GroupID,1) Client,
       LEFT(fieldValue,4) [Ship Year],
       COUNT(*) N
FROM dbo.vSystemInfoManual 
WHERE fieldName = 'Ship Date'
GROUP BY  PARSENAME(Machine_GroupID,1),
          LEFT(fieldValue,4);

Upvotes: 2

Related Questions