January 2, 2020 at 3:44 pm
I'm trying to write a query that will count (N) all members within a specific city but without repeating the name of the city (N) times... any ideas to what is semi-working now?:
SELECT city,
COUNT(PeopleID) OVER(PARTITION BY city) AS CountOfCities
FROM gcdf_DB..PeopleContactInfo
January 2, 2020 at 3:50 pm
SELECT city,
COUNT(*)
FROM gcdf_DB..PeopleContactInfo
GROUP BY City
SELECT city,
COUNT(DISTINCT PeopleID)
FROM gcdf_DB..PeopleContactInfo
GROUP BY City
One of these two, depending on whether or not a PeopleID can appear multiple times against one city.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2020 at 4:01 pm
Is there a way to order by COUNT like?:
New York 73,152
Chicago 34,152
Los Angeles 15,252
I'm using this as a learning exercise too, so if anyone has any PARTITION BY or WITH CUBE blocks of code that would teach me - greatly appreciated
January 2, 2020 at 4:03 pm
Sure
GROUP BY City ORDER BY COUNT(*)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2020 at 6:36 pm
I'm trying to sum the payments for each person and I also wonder if I can take it further joining other tables like location, etc. and using SUM, ROW NUMBER and PARTITION BY to extract data like payments by state:
My raw data looks like this:
1 120.00 CO
2 570.00 BC
3 215.00 FL
4 280.00 NC
5 1190.00 IA
SELECT distinct p.peopleId, SUM(p.Amount), s.stateAbbrveation
FROM
BCC_DB..payments p WITH(NOLOCK)
INNER JOIN
BCC_DB..peopleContactInfo i WITH(NOLOCK)
ON p.peopleid = i.peopleid
INNER JOIN BCC_DB..states s WITH(NOLOCK)
ON i.stateid = s.stateid
group by p.peopleId, s.stateAbbrveation
ORDER BY p.peopleID
January 2, 2020 at 7:39 pm
Please provide sample data in the form of a script to either create a temporary table or declare a table variable and an INSERT statement to insert values into said table. Your sample should also contain at least on person who has more than one payment and at least one state that has more than one person. We can't tell if the SUMs are working correctly if you only have one possible record for each group.
You also need to provide expected results by, again, providing a temp table/table variable and inserted values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 2, 2020 at 9:23 pm
To add on to drew's comments you probably want to be clear about which state you want. it looks like for now you're lumping it in by state of residence, not necessarily by state of point of sale.
As to your query - if you're leveraging the GROUP BY syntax, you typically do not need or want a DISTINCT on the column list as well, since the group by will provide at most one entry per unique combination of the GROUP BY columns. (it already handles the distinct for you). it can also lead to some monstrously confusing results if the columns of the group by aren't included in the SELECT statement.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply