sql count with group by

Question

I have the following query - which is doing as I would expect.

SELECT 
profile_name, 
COUNT(DISTINCT serial_number) AS num_profiles, 
payload_name, timestamp 
FROM profile 
WHERE 1 
GROUP BY profile_name, payload_name;

However, I need to group by profile_name, payload_name but only want to count the distinct serial_numbers by profile_name. So - I believe in the command above - it is counting dependent on both of the GROUP BY parameters. I want it to count dependent on the profile_name only. However, I still have to group by the profile_name, payload_name.

The following command counts how I want it to - but does not give me all the payload_name data I need.

SELECT 
profile_name,
 COUNT(DISTINCT serial_number) AS num_profiles, 
payload_name, 
timestamp 
FROM profile
WHERE 1 
GROUP BY profile_name;

Basically I need all the info from the first query with the count information from the second query.

Can I do this in one sql query or do I need to do two.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Mar 24, 2015 at 12:45

jarlh · Accepted Answer · 2015-03-24 12:42:18Z

3

A correlated sub-query for the count is an easy adjustment:

SELECT profile_name,
       (select COUNT(DISTINCT serial_number)
        from profile p2
        where p1.profile_name = p2.profile_name) AS num_profiles, 
       payload_name, 
       timestamp 
FROM profile p1

Perhaps you want do SELECT DISTINCT also?

answered Mar 24, 2015 at 12:42

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql count with group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related