Count

Hello sql expert,

I am trying to count ... and here are the query that I am working on:

Materials Unit# Status StatusDate CompleteddDate
Drywall H225 Return 01/17/2015 01/17/2015
Drywall H225 Repair 01/19/2015 01/22/2015
Drywall H225 Bought 02/02/2015 02/02/2015
Ladders H339 Return 01/05/2015 01/05/2015
Ladders H339 Bought 01/10/2015 01/10/2015
Drywall H225 Return 02/05/2015 02/05/2015
Ladders H339 Return 02/11/2015 02/11/2015
Ladders H339 Repair 02/20/2015 02/25/2015
Ceiling H405 Repair 05/02/2015 06/05/2015

So, what I want to do is to have a new field that count how many status on each material. For example: I want to be able to display how many status being Return, Repair and Bought on each Materials (like Drywall, Ladders, Ceiling) but also still showing the detail of the StatusDate and CompletedDate. If I remove the date fields, I can count on each status on each Materials but keeping the date as the detail, I need help! Anyone? So it would be like this:

Materials Unit# Status Count StatusDate CompleteddDate
Drywall H225 Return 2 01/17/2015 01/17/2015
Drywall H225 Repair 1 01/19/2015 01/22/2015
Drywall H225 Bought 1 02/02/2015 02/02/2015
Drywall H225 Return 2 02/05/2015 02/05/2015

Ladders H339 Return 2 01/05/2015 01/05/2015
Ladders H339 Bought 1 01/10/2015 01/10/2015
Ladders H339 Return 2 02/11/2015 02/11/2015
Ladders H339 Repair 1 02/20/2015 02/25/2015

Ceiling H405 Repair 1 05/02/2015 06/05/2015

Here is my query:
Select Materials, Unit#, Status, StatusDate, CompletedDate
From MaterialDB
Where StatusDate between '01/01/2015' to '09/30/2015

Thank you all

Select Materials, Unit#, Status, 
       [Count] = count(distinct Status) over (partition by Material, Unit#),
       StatusDate, CompletedDate
From   MaterialDB
Where  StatusDate between '01/01/2015' to '09/30/2015
1 Like

I got an error "Use of DISTINCT is not allowed with the OVER clause." Is "Over" can't be used together with distinct, Khtan?

This is what I am looking for but got an error because of the count distinct. The count distinct is what I need on the status but is there a way to solve the issue in [Count] = count(distinct Status) over (partition by Material, Unit#) between DISTINCT and PARTITION? Thanks Khtan.

You can find few workarounds for "Use of DISTINCT is not allowed with the OVER clause." here: https://connect.microsoft.com/SQLServer/feedback/details/254393/over-clause-enhancement-request-distinct-clause-for-aggregate-functions

1 Like
Select     Materials, Unit#, Status, [Count],
           StatusDate, CompletedDate
From       MaterialDB m
    Cross Apply
    (
        Select    [Count] = count(distinct Status)
        From    MaterialDB x
        Where    x.Materials    = m.Materials
        And    x.Unit#        = m.Unit#
    ) c
Where      StatusDate between '01/01/2015' to '09/30/2015
1 Like

Thank you Khtan. It is working now, now all the material users can get their data. Thanks again.