CODEWITHSUNDEEP
mysqlsql
dingerkingh
dingerkingh

Reputation: 448

sql count with group by

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.

Upvotes: 0

Views: 116

Answers (1)

jarlh
jarlh

Reputation: 44795

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?

Upvotes: 3

Related Questions