SQLTeam.com | Weblogs | Forums

Handle Total COUNT issue


#1

Hi,

I am trying to find what is the best approach to handle the case I am working on. What I am trying to do is to display all of the data/fields but add the count of how many time a material being sold and returned from our shop.

Here is my select statement:

Select MaterialName, MaterialType, MaterialTypeID, BuyerID, Status, StatusDate, DepartmentID, WarehouseRepID
From MaterialTable

This is the data from our material table:

So what I am trying to do is to create a list that shows count on each material type id status like below. There is a new field called " Status Count". This field is to count total of each MaterialType ID. For example: Material ID: CB013. This material being sold for 3 times on 03/21/2015, 06/11/2015 and 08/02/2015 but also customer returned this item for 3 times on 04/15/2015, 06/20/2015 and 08/28/2015. As you can see, in the Status count field, this item count as 3 on status Sold and 3 on status Returned. This logic is apply to other equipment's as well. It counts the total of the status. So this is what I am trying to do. Anyone could help?

Thank you


#2

Ok, so you have the data in a spread sheet and you want to run a query in SQL to return the results of the second table image


#3

That is correct SZ1. Thank you.


#4

A quick way, not sure if you want to elaborate.

Select          MaterialName,
		MaterialType, 
		MaterialTypeID,  
		BuyerID, 
		Status, 
		COUNT(MaterialTypeID) StatusCount,
		StartDate, 
		DepartmentID, 
		WarehouseRepID
			FROM @MaterialTable
			GROUP BY MaterialName, MaterialType, MaterialTypeID, BuyerID, Status, StartDate,  DepartmentID, 
		             WarehouseRepID

#5

Thanks SZ1 but we are also need to display the dates as well. Grouping those dates that is the issue we are working on. So, basically we want to count the Status but also be able to include the dates. The issue is, if we include the date, then each count will be 1 which is not we are trying to do. We want to be able to count total status count but displaying the dates will give us an idea when those status happened.

Thanks Sz1


#6

I edited the above query, please post some sample data...


#7

Hi Sz1,

thanks for the update. Again...adding the dates inside the count query will only give you single count because of the dates grouping. The count will count based on the grouping and it will only give you single count since the dates are included inside the query.

let's take one example:
Let's take a look at MaterialTypeID B1232. This iteme being sold for 3 times and if you take a look at the second sheet, the status count is 3 (so this count is what we are trying to achieve). However, the second sheet keep the detail from the sheet 1 but my status count is 3 on this item. The Status date will help us to see when the status being sold three times. The same apply to status Returned on the same items.

If I included the Status dates inside the query, all of my status count will only list 1, which is not we are trying to achieve.

I hope this is make sense Sz1.

Thanks


#8
SELECT MaterialName,
    MaterialType,
    MaterialTypeID,
    BuyerID,
    STATUS,
    StatusCount = count(*) OVER (
        PARTITION BY MaterialTypeID,
        STATUS
        ),
    StatusDate,
    DepartmentID,
    WarehouseRepID
FROM MaterialTable

#9

Hi there,

It worked! Thank you Khtan. You are very smart.