0

I've searched a number of posts looking for an answer and I can't seem to find one that quite explains a solution. I feel like this should be relatively simple:

I have a wedding database with a table which lists meal options (tbl_Meal_Options). For instance it has 1 column with 4 values: Meat, Fish, Chicken, Vegetarian.

A second table contains all the invitations that were sent out. Because there can be up to 4 guests on an invitation, each invite row has 4 meal columns: "Meal_1", "Meal_2", "Meal_3", and "Meal_4". The cells in those four columns are linked to the Meal_Options table. So as invites are received, the appropriate option is selected in Meal_1/2/3/4 as a dropdown linked to the Meal_Options table..

I would like to be able to run a query which shows the 4 items in the Meal_Options table and then tallies the number of times each item is selected among the Meal_1-4 columns.

Ultimately, I'm looking for something that looks like the following: Chicken 57 Fish 24 Meat 39 Vegetarian 14

The total number of meals should equal the total number of guests attending.

Any help is VERY much appreciated.

Argonax
  • 15
  • 5
  • Instead of describing your tables and data in prose, it would be more helpful if you can edit your question and add some sample data, using http://www.sensefulsolutions.com/2010/10/format-text-as-table.html . Just copy some data from an Access datasheet or from Excel, then format as "MySQL", copy the output to your question and format as code. – Andre May 01 '16 at 07:27

1 Answers1

0

(Since you never mentioned its name, I will call the second table tbl_Invitations in this answer)

SQL queries are easiest done on a database design that is normalized (see What are 1NF, 2NF and 3NF in database design? for what that means That actually does not fit to the problem, but I haven't found anything better after a quick search).

Having the same type of information (the meal option) spread out over 4 columns in tbl_Invitations should be avoided. Instead, create a third table with two columns (Invitation_ID and Meal_Option, the respective primary keys of tbl_Invitations and tbl_Meal_Options) holding a list of actual meals that should be serverd. The first column indicates to what invitation the meal belongs, the second one shows what meal option is desired.

Now you could simply use SELECT Meal_Option, COUNT(*) from that table, grouping on Meal_Option.

This would be the clean way, but you can also retrieve the same information using your current table layout:

SELECT m.Meal, COUNT(*) AS Orders
FROM tbl_Meal_Options AS m
LEFT JOIN (
    SELECT Meal_1 AS Meal FROM tbl_Invitations
    UNION ALL
    SELECT Meal_2 AS Meal FROM tbl_Invitations
    UNION ALL
    SELECT Meal_3 AS Meal FROM tbl_Invitations
    UNION ALL
    SELECT Meal_4 AS Meal FROM tbl_Invitations
) AS i
ON m.Meal_Option = i.Meal
GROUP BY m.Meal

(assuming Meal_Option is the primary column in tbl_Meal_Options and Meal the text description)

As you can see this is a litte bit more elaborate and scales badly if you want to change the number of meals per invitation (which you probably won't, but anyways...)

Leviathan
  • 2,468
  • 1
  • 18
  • 24
  • Perfect answer!! Thank you!! I had a feeling that 4 meal columns was not the best way to handle this, but I needed some way to enter up to four options using only the tables (no fancy forms developed). The ONLY problem I have is that the summary table now shows the ID numbers instead of the actual values. Instead of Meat, Fish, Chicken, etc. I get 1, 2, 3, 4. I'm sure it has to do with my linked list dropdown but I'm not sure how to fix that. Not the worst problem ever though. You solved my main issue!! – Argonax May 02 '16 at 03:38
  • @Argonax: I updated the query to incorporate the meal name. – Leviathan May 02 '16 at 07:39
  • Thank you tremendously! I'd give your post 2 "checks" if it would let me! – Argonax May 02 '16 at 14:30
  • Well, you *can* upvote my answer by clicking the up arrow. :) – Leviathan May 02 '16 at 14:35
  • I certainly did. I just don't have enough "street credit" around here yet for it to count. I'm working on it. ;) – Argonax May 02 '16 at 14:40
  • Oh, yeah, you're right, I forgot. Well then, I wish happy asking and answering. Not just for my sake. :) – Leviathan May 02 '16 at 14:49