CODEWITHSUNDEEP
sqlsql-server
OurBG
OurBG

Reputation: 597

Grouping by two parameters in SQL Server

I have a table with this structure:

id | timestamp | barcode

The timestamp is datetime and the barcode is a full barcode, from which I need only the first 9 digits.

I need to get the count for each product for each day with one query.

So I basically need a result like:

date       | item number | count
-----------+-------------+--------
12.02.2019 | 827384950   | 32

Item number is left(barcode, 9).

Upvotes: 0

Views: 36

Answers (2)

hamid_reza hobab
hamid_reza hobab

Reputation: 929

this query has better performance

select date, itemnumber, count(*) from
(select cast([timestamp] as date) as date,left(barcode, 9) as itemnumber
from tablename) a
group by date,itemnumber

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

You can try below - using cast() to convert timestamp to date and then add that in group by 

select cast([timestamp] as date),left(barcode, 9) as itemnumber,count(*)
from tablename
group by cast([timestamp] as date),left(barcode, 9)

Upvotes: 2

Related Questions