Need Database query/reporting help

[Lany Freelove Cassandra]

I am trying to run when I’ve barely learned to crawl.    (sorry if this is long)

 

I am teaching myself to use our database at work and be able to build reports with the information my boss is looking for.  I have had 2 successes, but I can tell from looking at already created reports I did things “the long way”.  I’m ok with that since I have verified my data is correct.

 

My previous DB experience is nearly none, and I may not fully understand technical jargon. I took two on-line courses offered by my company on how to pull the data, but that is it. (it’s possible my Query is faulty?)

 

The formulas all seem to be similar to excel ones (from what I can see trying to find help on-line)

 

I think my problem right now is that I have two variables I want to sort by: dates and deal name.

 

I have a report that pulls all the data I need, but I can’t find/come up with the formulas that will put it into the format I need.

 

I need each deal name listed only once, with a count of the number of items, not the name of the item.

 

Part one: list only the unique names

 

Part two: a count of items associated with each unique name

 

I won’t know in advance what the names of the deals in the date range are, so I’m not sure how to come up with the count without knowing what the variable name is.

 

I feel like it is on the tip of my tongue like …if something (dealname?) =/ something(previous dealname?) than “count” or “numberRowsData(DealName)”

 

(I can manually do it after I have pulled the report, but I want it to do it automatically. (plus, when your report is 12,000+ lines long, scrolling to find all the deal names is cumbersome)

 

Is it even possible to do the second part?

 

 

In case I described it badly, this is a visual representation of what I mean:

 

What I have is:

Deal 1	Item 1
Deal 1	Item 2
Deal 1	Item 3
…
Deal 2	Item 47
Deal 2	Item 48
…
Deal 14	Item 123
Deal 14	Item 124
Deal 14	Item 125

 

What I need is:

 

Deal 1	(count of # of items) 46
Deal 2	31
…
Deal 14	12

 

I appreciate any help!

[Iskaral Pust]

"Group by" is what you need in your SQL statement, and count rather than sum on the group.

I don't know what type of database you are using, but SQL is pretty similar across all, whether you are typing SQL code or using a spreadsheet type user interface to select fields.  A quick google search will show you a sample of the syntax.

Good luck.

[Happy Ent]

As IP says, it helps if you tell us what system you are on.

[Lany Freelove Cassandra]

My system is Business Objects

 

I managed to get my deals listed individually, now still working on the formula to count the specific items associated with each deal.

 

I guess I need to know the right "operator" to add to my formula.

if I put in =NumberOfRows([Issue ID])ForEach([Deal Name]) I get an error message. The first part works, but it gives me the total number in all the fields, not just the total for that deal. (so all fields were populated with 12,688)

[Iskaral Pust]

4 minutes ago, Lany Freelove Cassandra said:

My system is Business Objects

 

I managed to get my deals listed individually, now still working on the formula to count the specific items associated with each deal.

Just just "group by" with "count", e.g.

general syntax for SQL is:

SELECT deal_name, count(deal_ID)
FROM Table_deals
WHERE deal_date > 20160100
GROUP BY deal_name

That code would take all deals from 1/1/2016 onward and give you a list of deal names with a count of how many deals there were of that name.  Modify as needed for your actual names of tables and data fields.

You don't need a specific formula to get the count.  Using count() as one of the query data fields in conjunction with "group by" will give it to you automatically. You just need to have a field you can count.  Any field that varies uniquely within a given grouping of deal_name is ok to use.  It could be entry_#, deal_id, date (if you know dates are unique), etc.

If you need to be dynamic with the date range for this query, then don't hard-code the date cut-off like I did. Use WHERE deal_date > start AND deal_date<end, then allow the user to specify start and end cut-offs.

Use google to look at simple SQL examples.  This is an entry-level function of database functions.  It shouldn't be hard to do on any platform.

[Happy Ent]

19 minutes ago, Lany Freelove Cassandra said:

My system is Business Objects

OK, Lany. What you’re looking for is “SQL,” a domain-specific “programming” language for databases. You probably only need the query syntax. This should help you googling for things. You want to know “How do I do BLABLA in SQL?” for various values of BLABLA. Or maybe “How the fuck do I BLABLA in SQL.” It’s pretty learnable. (I use it so seldom that I need to understand it from scratch each time.)

I can see that @Iskaral Pust has responded, so you’re in good hands. I’ll bow out.

[Lany Freelove Cassandra]

HA!  I got it.  Thank you.  

 I was trying to put in too much information to my formula.  When I got rid of all but the "count(issue ID) it worked. Just like me to try and over complicate matters  

 

And it may be simple and basic, but it is my first time creating queries and reports, and no one else I work with can really do them either. (I'm the "young one" here, so there's not a lot of computer knowledge on the team  

 

 

[Iskaral Pust]

I'm glad it worked.  Small errors in code are just as unproductive as big ones, and even more frustrating. 

If you hit further wrinkles, there are loads of easy resources for SQL online.  Just type your question into google.  I realize that half the problem is knowing how to frame the question.  SQL queries usually only have 5-6 different command lines -- SELECT, FROM, WHERE, GROUP BY, SORT BY -- available so once you know what they are and what they do, it's easier to focus your question.