8 Replies - 566 Views - Last Post: 06 August 2016 - 12:49 PM

#1 ShadesOfSepia  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-August 16

I Need Help With SQL Query For Class

Posted 06 August 2016 - 07:57 AM

Hi, I'm new here, and I'm not sure if this is the correct spot to post this, but......


My teacher wants me to pull all the customer emails from said date, then add up the total purchase for each email. Now, I got the first part of the query working (which pulls all the emails and purchase for said day. HOWEVER, now need to know how do I break it down, to show the total purchases for each email (customer). Any help would be appreciated.

Please see attached screenshot

Posted Image

This post has been edited by andrewsw: 06 August 2016 - 08:00 AM
Reason for edit:: fixed image


Is This A Good Question/Topic? 0
  • +

Replies To: I Need Help With SQL Query For Class

#2 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6336
  • View blog
  • Posts: 25,545
  • Joined: 12-December 12

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 08:07 AM

The first thing you need to do is to study table joins. What you have currently is using a cross join, spuriously displaying all options and menu prices. There must be something in the orders table that relates to something in the menu table, so that you are pulling up the correct price(s).
Was This Post Helpful? 0
  • +
  • -

#3 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 08:35 AM

GROUP BY would also help in your endeavor.
Was This Post Helpful? 0
  • +
  • -

#4 ShadesOfSepia  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-August 16

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 10:47 AM

View Postandrewsw, on 06 August 2016 - 08:07 AM, said:

The first thing you need to do is to study table joins. What you have currently is using a cross join, spuriously displaying all options and menu prices. There must be something in the orders table that relates to something in the menu table, so that you are pulling up the correct price(s).


Thank you Andrew,

I remember my teacher told me that I can use either "join" Or the "where" clause; and the "where" clause just seemed so much easier. But I also remembered when she said that there will be cases when you have to use join instead of where; I think this may be one of those circumstances. I was kind of avoiding using joins because I am using an older version of MySQL. Oh well, I'm looking over my notes for join now. Thank you so much. If I have more questions I'll post back.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6336
  • View blog
  • Posts: 25,545
  • Joined: 12-December 12

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 11:07 AM

WHERE can be used in place of a join, although a join is preferred, but you aren't applying it correctly. To achieve this the WHERE clause would need to relate fields between the two tables.

How old can your version of MySQL be? "The first version of MySQL appeared on 23 May 1995." Regardless, it will still support joins.
Was This Post Helpful? 0
  • +
  • -

#6 ShadesOfSepia  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-August 16

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 12:16 PM

Hi Andrew,

Thank you so much for your patience, AND guidance. Your clues REALLY helped to jar my memory. I have figured out the correct syntax for the join using the "WHERE" clause. The results matches what the mockup that my teacher has given me.

Posted Image

My version of SQL is 5.2.47CE, granted it's not that old; however, the course material that my teacher gave us, included so many exclusions and possible version conflicts to certain commands within MySQL that I kind of felt like it hindered my learning a quite a bit (just my opinion). That's way I kind wanted to block out joins temporarily. However, your strong recommendation helped me to understand the bigger picture. You've been a HUGE help. I may have a last question later. :stuart:/>

This post has been edited by andrewsw: 06 August 2016 - 12:20 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6336
  • View blog
  • Posts: 25,545
  • Joined: 12-December 12

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 12:22 PM

Glad to help.

Please copy and paste SQL statements directly here in the forum, not just as screenshots. Put them in code tags.

Topic moved to MySQL forum.

Quote

My version of SQL is 5.2.47CE

I think that is the version number of the Workbench, not of MySQL. The version isn't too significant though.
Was This Post Helpful? 0
  • +
  • -

#8 ShadesOfSepia  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-August 16

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 12:40 PM

LOLOL. It was a "force of habit." On my last job, I used to capture a lot of screenshots for for clients. I guess it will take me a little bit to break out of it.

This post has been edited by andrewsw: 06 August 2016 - 12:49 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6336
  • View blog
  • Posts: 25,545
  • Joined: 12-December 12

Re: I Need Help With SQL Query For Class

Posted 06 August 2016 - 12:49 PM

You also don't need to quote the previous post ;) there is a Reply button further down the page, or use the Fast Reply box.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1