none
Using Count function without aggregated query

    Question

  • Hello,

    I'm trying to use Count function in a query where I do not want to have aggregates. My query looks as follows:

    SELECT     Customers.FirstName, Customers.LastName, Customers.PrivateNumber, Branches.Name, Pawns.ID, Pawns.StartDate, Pawns.FinishDate, Items.Name, Items.Mass, 
                          Items.Quantity, Pawns.CurrentSum, Transactions.[Percent], Currencies.Name as Currency
    FROM         Customers INNER JOIN
                          Pawns ON Customers.ID = Pawns.CustomerID INNER JOIN
                          Items ON Pawns.ID = Items.PawnID INNER JOIN
                          Branches ON Pawns.BranchID = Branches.ID AND Pawns.LocationID = Branches.ID INNER JOIN
                          PawnTypes ON Pawns.PawnTypeID = PawnTypes.ID INNER JOIN
                          Currencies ON PawnTypes.CurrencyID = Currencies.ID LEFT OUTER JOIN
                          Transactions ON Pawns.ID = Transactions.PawnID AND Pawns.FinishDate = Transactions.Date
    WHERE     (Pawns.StatusID = 6) AND (Pawns.FinishDate BETWEEN '15Oct2013' AND '23Oct2013')
    ORDER BY Pawns.ID, Branches.Name, Pawns.FinishDate DESC



    The output looks like this:

    FirstName LastName PrivateNumber Name ID StartDate FinishDate Name Mass Quantity CurrentSum Percent Currency
    გიორგი კაჭარავა 1019018116 წერეთელი 9905 22.07.2012 11:21 2013-10-19 00:00:00.000 სამაჯური 3.7 1 55 4 აშშ დოლარი
    გიორგი კაჭარავა 1019018116 წერეთელი 11929 23.11.2012 14:15 2013-10-19 00:00:00.000 სამაჯური 5.3 1 116 7.17 აშშ დოლარი
    ნატალია კურიატნიკოვა 65002012999 გლდანი 14089 11.04.2013 13:49 2013-10-17 00:00:00.000 ბეჭედი თვლიანი 3.7 1 173 10.69 აშშ დოლარი
    ნატალია კურიატნიკოვა 65002012999 გლდანი 14089 11.04.2013 13:49 2013-10-17 00:00:00.000 ბეჭედი თვლიანი 4.9 1 173 10.69 აშშ დოლარი

    Now I want to add a column where I'll have the counted value of each ID (you can see that some of the IDs are identical). How can I do it without aggregating the whole table? (In this case, as far as I'm guessing I will not be able to select all the fields I need).

    Thank you in advance.

    Ivane 

    Wednesday, October 23, 2013 4:48 PM

Answers

  • SELECT     Customers.FirstName, Customers.LastName, Customers.PrivateNumber, Branches.Name, Pawns.ID, Pawns.StartDate, Pawns.FinishDate, Items.Name, Items.Mass, 
                          Items.Quantity, Pawns.CurrentSum, Transactions.[Percent], Currencies.Name as Currency,
    COUNT(1) OVER (PARTITION BY Pawns.ID) AS Cnt
    FROM         Customers INNER JOIN
                          Pawns ON Customers.ID = Pawns.CustomerID INNER JOIN
                          Items ON Pawns.ID = Items.PawnID INNER JOIN
                          Branches ON Pawns.BranchID = Branches.ID AND Pawns.LocationID = Branches.ID INNER JOIN
                          PawnTypes ON Pawns.PawnTypeID = PawnTypes.ID INNER JOIN
                          Currencies ON PawnTypes.CurrencyID = Currencies.ID LEFT OUTER JOIN
                          Transactions ON Pawns.ID = Transactions.PawnID AND Pawns.FinishDate = Transactions.Date
    WHERE     (Pawns.StatusID = 6) AND (Pawns.FinishDate BETWEEN '15Oct2013' AND '23Oct2013')
    ORDER BY Pawns.ID, Branches.Name, Pawns.FinishDate DESC

    Wednesday, October 23, 2013 4:51 PM

All replies

  • SELECT     Customers.FirstName, Customers.LastName, Customers.PrivateNumber, Branches.Name, Pawns.ID, Pawns.StartDate, Pawns.FinishDate, Items.Name, Items.Mass, 
                          Items.Quantity, Pawns.CurrentSum, Transactions.[Percent], Currencies.Name as Currency,
    COUNT(1) OVER (PARTITION BY Pawns.ID) AS Cnt
    FROM         Customers INNER JOIN
                          Pawns ON Customers.ID = Pawns.CustomerID INNER JOIN
                          Items ON Pawns.ID = Items.PawnID INNER JOIN
                          Branches ON Pawns.BranchID = Branches.ID AND Pawns.LocationID = Branches.ID INNER JOIN
                          PawnTypes ON Pawns.PawnTypeID = PawnTypes.ID INNER JOIN
                          Currencies ON PawnTypes.CurrencyID = Currencies.ID LEFT OUTER JOIN
                          Transactions ON Pawns.ID = Transactions.PawnID AND Pawns.FinishDate = Transactions.Date
    WHERE     (Pawns.StatusID = 6) AND (Pawns.FinishDate BETWEEN '15Oct2013' AND '23Oct2013')
    ORDER BY Pawns.ID, Branches.Name, Pawns.FinishDate DESC

    Wednesday, October 23, 2013 4:51 PM
  • Thank you very much!

    Can I ask you one more: Now I'd like to add another column where I'd calculate some values like this:  If  Cnt>1, (Items.Mass/ SUM(Transactions.Percent)* Transactions.Percent, Else Transactions.Percent as PercentDistrNew 

    but it doesn't work for me (perhaps I'm using the wrong syntax or something else is not ok - I'm really bad at sql data mining).

    Could you help in finding a correct solution for that as well?

    Wednesday, October 23, 2013 5:25 PM