CODEWITHSUNDEEP
mysqlsqljoingroup-bycount
K997
K997

Reputation: 479

Complex SQL Query with two tables and group by count

I need to write a complex query that involves two tables and I’m struggling to understand how to construct the query properly.

The table structure is as follows, along with sample data:

Table 1:
ID | Type | Size
A123 | Block | Medium
C368 | Square | Large
X634 | Triangle | Small
K623 | Square | Small

Table 2:
ID | Code | Description | Price
A123 | C06 | Sensitive Material | 99.99
A123 | H66 | Heavy Grade | 12.76
A123 | U74 | Pink Hue | 299.99

C368 | H66 | Heavy Grade | 12.76
C368 | G66 | Green Hue | 499.99
C368 | C06 | Sensitive Material | 99.99
C368 | K79 | Clear Glass | 59.99

X634 | G66 | Green Hue | 499.99
X634 | K79 | Clear Glass | 59.99
X634 | Z63 | Enterprise Class | 999.99

K623 | K79 | Clear Glass | 59.99
K623 | G66 | Green Hue | 499.99
K623 | X57 | Extra Piping | 199.99

The query should be based on the Type column from Table 1 primarily and then total the counts from Table 2 based on matching IDs.

The final output should be a count that looks like this for Type = Square:

Code | Description | Count
H66 | Heavy Grade | 1
G66 | Green Hue | 2
C06 | Sensitive Material | 1
K79 | Clear Glass | 2
X57 | Extra Piping | 1

How can I assemble this result using these two tables? Here is an initial query I've written - does this look correct? It produces a result similar to the example above, however I'm not certain it is 100% correct and captures all edge cases.

select code, description, count(*) as total
from
(
SELECT code, description
FROM db.options_list ops
INNER JOIN db.obj_list ON ops.ID = db.obj_list.ID
WHERE db.obj_list.type="Square"
) src
group by code
order by total desc

Thanks

Upvotes: 2

Views: 357

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

If I understand correctly, this is just a simple join/group by query:

SELECT ops.code, max(ops.description), count(*) as total
FROM db.options_list ops JOIN
     db.obj_list ol
     ON ops.ID = ol.ID
WHERE ol.type = 'Square'
GROUP BY ops.code
ORDER BY total desc;

Notes:

  • All tables use table aliases.
  • All column references are qualified.
  • The delimiter for the string comparison is the standard single quote, rather than the non-standard double quote.
  • All unaggregated columns in the SELECT are in the GROUP BY.
  • The subquery is unnecessary.

Your query might work in some databases. I do encourage you to follow best practices when writing queries, though.

Upvotes: 1

GMB
GMB

Reputation: 222432

You seem to want joins and aggregation:

select op.type, ob.code, ob.description, count(*) cnt
from options_list op
inner join obj_list ob on op.id = ob.id
group by ol.type, ob.code, ob.description

Upvotes: 0

Related Questions