troubleshooting Question

Need help creating appropriate SQL query for a table in MySQL

Avatar of fsyed
fsyed asked on
DatabasesMySQL ServerSQL
5 Comments2 Solutions397 ViewsLast Modified:
Dear fellow developers:

I need help generating an appropriate SQL query to retrieve results from a table in a MySQL database.  At present, I am working with a table called "sales", which has two relevant columns, "ID", and "Brand".  There are 4 distinct "Brand's" in the table, "A", "B", "C", "D", and there are hundreds of "ID's".  Each "ID" may have any number of "Brands" associated with it, (e.g. ID=1111, may have 43 Brand "A" associated with it, 23 Brand "B" associated with it, 42 Brand "C" associated with it, and 3 Brand "D" associated with it).  Every ID is like this.  So our table would like this:

Table: Sales
--------------

ID                Brand
1111             B
1111             A
1111             D
1111             B
1111             C
1111             A
1111             C
1111             B
1111             D
1111             A
...
9999            A
9999            B
9999            A
9999            A
9999            C
9999            B
9999            A
9999            B
9999            C

What I need to do, is simply select each distinct ID, and a count of each brand associated with it in parallel columns, so that I get a result set that looks like this:

ID                A         B        C          D
1111           3         2         5          8
...
9999           4         3         8          1

Can anyone help me construct an SQL query that will accomplish this?  At present I am able to only come up with my query below, but that means I have to go through each "ID", and each "Brand" one at a time, which is making this VERY tedious.  There are no primary keys in this table.

Thanks in advance to all who reply.
SELECT COUNT(*) FROM `sales` WHERE `ID`="1111" AND `Brand`="A";
ASKER CERTIFIED SOLUTION
Psyberion

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros