CODEWITHSUNDEEP
sql-serverjoincountgroup-by
David White
David White

Reputation: 141

SQL Server group and count

I have two tables:

table machines
-------------- 
machineID
FaNo
Barcode
RoutingCode
Name


table log
-------------
logID
lineBarcode
machineBarcode

So, I'm trying to select all the machines through log where lineBarcode is something specifed.

SELECT * FROM machines JOIN log ON log.machinebarcode = machines.barcode WHERE log.linebarcode = 100000000001

There are some records in machines which have same routingCode which means they are the same model.
How can I get there count separately?
I mean I need to get something like

Result
---------------------------------------
RoutingCode     Name          Count

B4            Machine1         10
C8            Machine21         7
D1            Machine12        43

So at all the query without group will have 50 records in return and the idea is to group them by routingCode and get their count from the query.
Is this possible and if is how? :)

Upvotes: 1

Views: 103

Answers (2)

BlackICE
BlackICE

Reputation: 8926

SELECT routingcode, name, count(1)
FROM machines 
LEFT OUTER JOIN log ON log.machinebarcode = machines.barcode
WHERE (log.linebarcode = 100000000001) OR (log.linebarcode is null)
GROUP BY routingcode, name

Upvotes: 3

EternalWulf
EternalWulf

Reputation: 773

SELECT RoutingCode, Name, COUNT(logID) AS 'Count'
FROM machine
JOIN log ON log.machinebarcode = machine.barcode
WHERE log.lineBarcode = 100000000001
GROUP BY RoutingCode, Name

This will give you the result you wanted.

Good luck*

Upvotes: 0

Related Questions