Question

Locked

Can someone with SQL knowledge help me out?

By klondikegurl ·
So the question goes like this:

NAME DATE PRESENTS
bob march 3 2011 1
bob june 6 2008 2
bob jan 3 2012 3
mary feb 14 1986 4
mary april 10 2001 5
mary jan 3 2012 6
kate march 3 2011 7
kate jan 3 2012 8
kate oct 9 2013 9
celia march 3 2011 10
celia feb 14 1986 11
celia july 4 2011 12
celia jan 3 2012 13
celia feb 14 19** 14

So the goal is we add the amount of presents kate and celia got on the same days if they got any on the same days
What I need to do is kind of like this except with a much bigger data set. Prolly in the 100 000 entries.
i need the answer in SQL code???? or access 2003 query SQL

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

homework??? - NT

by PurpleSkys In reply to Can someone with SQL know ...
Collapse -

Reponse To Answer

by klondikegurl In reply to homework??? - NT

Hey nope its not homework. I was asked to do this for my summer job. and im a student with no access knowledge at all from before so I'm starting from scratch and made up this example that is related to what I have to find out for work. Ive been googling for two days on forums and sites on SQL and queries but have not gotten anywhere =( not a very tech smart girl. So eventually i resulted to making up this example and posting it in search of help

Collapse -

Reponse To Answer

by klondikegurl In reply to homework??? - NT

i deal mostly with data maanagement thats why the question is as it is. I wish it were homework and i were a student learning this language maybe i would have a bit more of an idea on what to do or where to start >.>. From these two days of googling I'm still not sure whether i can write all the qrite criteria for a single query. use a few diff queries ... with no idea on how to connect them. nest subqueries or I don't evenkno >.>

Collapse -

Reponse To Answer

by Tony Hopkinson In reply to homework??? - NT

It would be pointless doing it for this example then, you wouldn't understand enough to apply it. There are professional developers (lots of them) who couldn't do this. Nobody in the known universe could optimise it without knowing the environment it's going to exist in.
I can think of five different approaches off the top of my head.
This is summer job as in by the time you've learnt and done it will be autumn, not your fault whoever tasked you with this is a git, a fool, ot it's test in and of itself and they want you to go back and say no I can't do this, ie a clever git.

Collapse -

Well I could do it for you, but then you

by Tony Hopkinson In reply to Can someone with SQL know ...

wouldn't learn anything, and it looks like home work to me. Though moderately difficult homework I'll admit.

The trick with these sort of queries where your schema doesn't match your usage requirements is to break them up.

First thing to do is to chop out all the records not for Celia and Kate, ie get rid of Bob and Mary.

Then to get the days where Celia and Kate both got a present e.g.
Select [Date],Count(date) as NumberOfTimes From KateandCelia Group By [Date]

Then join that back to KateAndCelia on Date where NumberOfTimes > 1

Then select Date, Sum(Presents) From Above.

The trick is to get the Kate and Celia records first, otherwise you'll be all over the place.

In access you'd just create intermediate queries and use them in a cascade, and it might be easier for you to develop that way.

In raw SQL you could use temporary tables either with select into or by creating them and using Insert #someable select ...

or there's this nifty trick

Select [Date] From (Select [Date],Count(*) as SomeField From MyTable) dummyTableName Where SomeField > 1

dummyTableName can be anything (as long as it's unique), without it you get a syntax error.

Do it in bits though, check the bits, don't try and blurt it all in one go.

HtHs

PS whoever set you this question, get them to stop using sql keywords for sql objects, it's a [PIA].


PPS there is is an easier way but it's an inflatable dartboard solution

You could get all the records for Celia, and all the ones for Kate and then do a join on date which would give you somnething like
Kate 3/3/2011 7 Celia 3/3/2011 10
...
...
Then select Date, KatePresent + CeliaPresent from above
But if the next question is for Bob and Celia you have a problem...

Optimising it for 100k records depends on the database, access is going to suck next to say SQL Server whatever you do.

Collapse -

Reponse To Answer

by klondikegurl In reply to Well I could do it for yo ...

Hey Tony,

Please read my reply to PurpleSkys above. and thank you for your valuable input! I havent had time to start googling on how to do the stuff you said because I jsut got back from a weekend vaca but hopefully I'll be able to think my way thru your directions . thank you! I'll try !

I will not lie I just read your directions and I have no idea what they mean <--- I have never done any work with SQL and ms access befoe >.> but this task was requested of me at work with the directions being to figure out! I'll try but if you can be so kind as to be mroe specific with what the code will look like or anything? i have no idea.... i figured out macros the for the first task but this so far is beyond me. I feel like i need a whole course on sql before i begin a task liek this instead of the few days googling time im getting.

Collapse -

Reponse To Answer

by klondikegurl In reply to You have a reply at this ...

o trust me if i didnt have a trip planned before for this weekend i wouldve already had. thanks

Collapse -

the answer without any optimization (access 2003 sql)

by fgnbmcs In reply to Can someone with SQL know ...

select date, name, sum(presents) as amount
from data
where date in (
SELECT Data.DATE
FROM Data
GROUP BY Data.DATE
HAVING Count(Data.PRESENTS)>1
) and (Data.NAME="kate" Or Data.NAME="celia")
group by date,name

Collapse -

SQL solution

by danysch In reply to Can someone with SQL know ...

Here it is, for every date where both Kate and Celia received presents show how many presents they received each.

select c.date,sum(c.presents) CeliaPresents,
sum(k.presents) KatePresents from
(select date,presents from PRESENT where name
='Cella') c ,
(select date,presents from PRESENT where name
='Kate') k
where k.date=c.date group by c.date order by c.date ;

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums